Shareware Grab Bag

home *** CD-ROM | disk | FTP | other *** search

/ Shareware Grab Bag / Shareware Grab Bag.iso / 090 / cshlgr.arc / CSHLGR.DOC < prev next >

Wrap

Text File | 1986-04-11 | 159.4 KB | 4,422 lines

The CashFlow Ledger (tm), Version 1.0 by Patrick T. Magee, CPA PREFACE (C) Copyright 1985 by Altair Systems. All Rights Reserved. The CashFlow Ledger is a macro-driven Lotus 1-2-3 template (Symphony version also available) that integrates a simplified short term cash flow management system, with a simplified general ledger system. All macro routines in this template revolve around a single transaction database which can hold over 1600 transactions per time period, and over 100 different account numbers, on a 256k computer. The CashFlow Ledger is being marketed using the shareware method as described below. Consequently, this disk contains the complete application and manual. I'll Pay You to Copy The CashFlow Ledger You are free to share unmodified copies of The CashFlow Ledger and this documentation with others. Should you find this application useful and wish to support this work, you may register a copy for $75 and receive the following: A $25 commission every time someone else registers a copy of your registered diskette. A printed typeset copy of the manual Free copy of the next major release of The CashFlow Ledger Phone Support To register, fill out the registration form contained in the file, REGISTER.PRN, and send it along with $75 to: Altair Systems 1711 - 143rd Place SE, Suite C1 Bellevue, WA 98007 (Washington State residents please add sales tax.) Please do not use this address after 9/1/86 (with emphasis on the 86). About the Author Patrick Magee is a CPA based in Bellevue, WA specializing in the application of Lotus 1-2-3 and Symphony to business problems. He acquired his Lotus knowledge by helping over 40 businesses implement Lotus products and has over 500 hours experience teaching others how to use them. He writes a monthly column for Absolute Reference - The Journal for 1-2-3 and Symphony Users (1-800-227-7999). He is also the founder and president of the Pacific NW Lotus Users Group. DISCLAIMER THE CASHFLOW LEDGER IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE CASHFLOW LEDGER IS WITH YOU. YOU ASSUME THE RESPONSIBILITY FOR THE SELECTION OF THE CASHFLOW LEDGER TO ACHIEVE A PARTICULAR RESULT YOU DESIRE, AND FOR THE INSTALLATION, USE, AND RESULTS OF OPERATING THE CASHFLOW LEDGER. IN NO EVENT WILL PATRICK MAGEE OR ALTAIR SYSTEMS BE LIABLE TO YOU FOR ANY DAMAGES, INCLUDING ANY LOST PROFITS, LOST SAVINGS, OR OTHER INCIDENTAL OR CONSEQUENTIAL DAMAGES ARISING OUT OF THE USE OF OR INABILITY TO USE THE CASHFLOW LEDGER, EVEN IF PATRICK MAGEE OR ALTAIR SYSTEMS HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES, OR FOR ANY CLAIM BY ANY OTHER PARTY. TRADEMARK NOTICES Lotus 1-2-3 is a trademark of Lotus Development Corporation CashFlow Ledger is a trademark of Altair Systems DEDICATION The CashFlow Ledger is dedicated to my Grandmother, Thelma Magee, for her faith, and for the first time so long ago that she said to me, "You write so well". Table of Contents Chapter 1 - Introduction ............................................. 1 Purpose ......................................................... 1 Background on The CashFlow Ledger ............................... 1 Warning to Beginning Lotus 1-2-3 Users .......................... 1 Guide to Using the Rest of the Manual ........................... 2 Getting Started ................................................. 2 Chapter 2 - Introductory Tutorial .................................... 5 Purpose ......................................................... 5 Getting Started ................................................. 5 Entering a Transaction .......................................... 6 Sorting the Database ............................................ 6 Viewing the Modify Help Screens ................................. 7 The Query Data Options .......................................... 7 The Summarize Options ........................................... 8 Printing Summary Reports ........................................ 8 The Cash Flow Summary Options ................................... 8 Chapter 3 - Setting up The CashFlow Ledger .......................... 10 Purpose ........................................................ 10 Loading ........................................................ 10 Initializing ................................................... 10 Is it Cash Flow or Ledger ...................................... 11 Maintaining Multiple Ledgers ................................... 11 Entering Transactions .......................................... 11 The Current Year and Month .................................. 12 The Alt-A Macro ............................................. 12 Other Alt-Letter Commands ................................... 12 Recalculation Method ........................................ 12 Recurring Transactions ......................................... 15 Chapter 4 - Reference Section ....................................... 15 1 Modify Options .................................................. 15 1.1 Add/Edit .................................................... 15 1.2 Sort ........................................................ 18 1.3 Balance ..................................................... 18 1.4 Date ........................................................ 19 1.5 Print ....................................................... 19 1.6 File ........................................................ 19 1.6.1 Xtract ................................................ 19 1.6.2 Retrieve .............................................. 19 1.6.3 Quit .................................................. 20 1.7 Help ........................................................ 20 1.8 Quit ........................................................ 20 2 Query Options ................................................... 20 2.1 Account ..................................................... 21 2.2 Date_Range .................................................. 21 2.3 Both ........................................................ 21 2.4 Freeform .................................................... 21 2.5 Scan ........................................................ 22 2.6 Print ....................................................... 22 2.7 Help ........................................................ 22 2.8 Quit ........................................................ 22 3 Summarize Options ............................................... 22 3.1 All ......................................................... 22 3.2 Date_Range .................................................. 23 3.3 Xtract ...................................................... 23 3.4 Scan ........................................................ 23 3.5 Print ....................................................... 24 3.5.1 Summary ............................................... 24 3.5.2 Account ............................................... 24 3.5.3 Date_Range ............................................ 24 3.5.4 Quit .................................................. 24 3.6 Help ........................................................ 24 3.7 Quit ........................................................ 25 4 Cash Flow Summary Options ....................................... 25 4.1 Calculate ................................................... 25 4.2 Graph ....................................................... 25 4.2.1 Cash_Flow ............................................. 25 4.2.2 Daily_Balance ......................................... 25 4.2.3 Save .................................................. 26 4.2.4 Quit .................................................. 26 4.3 Date ........................................................ 26 4.4 Scan ....................................................... 26 4.5 Print ....................................................... 26 4.6 Help ........................................................ 26 4.7 Quit ........................................................ 26 5 Setup Options ................................................... 26 5.1 Recur_Trans ................................................. 27 5.1.1 Add/Edit .............................................. 27 5.1.2 Print ................................................. 28 5.1.3 Help .................................................. 28 5.1.4 Quit .................................................. 28 5.2 Date ........................................................ 28 5.3 Init ........................................................ 28 5.4 Formfix ..................................................... 28 5.5 EraseHelp ................................................... 28 5.6 X/C ......................................................... 29 5.6.1 Expand_Query .......................................... 29 5.6.2 Expand_Summary ........................................ 29 5.6.3 Contract_Query ........................................ 29 5.6.4 Contract_Summary ...................................... 30 5.6.5 Quit .................................................. 30 5.7 Help ........................................................ 30 5.8 Quit ........................................................ 30 6 On-line Help .................................................... 30 7 Save Options .................................................... 30 7.1 Save ........................................................ 31 7.2 Directory ................................................... 31 7.3 Quit ........................................................ 31 Appendices .......................................................... 32 A - Listing of Alt-Letter Macros ............................... 32 B - Range Name Listing ......................................... 35 C - Summary of Reporting Options ............................... 37 D - Building the Acccount Summary Macro Routines ............... 41 E - Building the Cash FlowSummary Routines .................... 51 F - Trouble Shooting ........................................... 62 Chapter 1 Introduction PURPOSE This chapter was designed to be your guide to the rest of this manual. It explains the purpose of each section and suggests some possible ways of using them. This chapter is organized as follows: - Background on the Product - Warning to Beginning Lotus 1-2-3 users - Guidelines to using the rest of the manual - Getting Started BACKGROUND ON THE CASHFLOW LEDGER Two fundamental problems faced by all businesses are managing short term cash flow and maintaining ledgers of some kind. In November of 1983, I published "CashFlow For 1-2-3", a Lotus 1-2-3 template for managing short term cash flow. It wasn't real hard to tell that this was a fundamental problem. I had just started my own business, and I had a "fundamental" problem with my cash flow. CashFlow For 1-2-3 was the tool I developed to squeak by. In February of 1984, the owner of a local CPA firm called me up to see if I knew a way to use 1-2-3 to post several hundred transactions to approximately 60 different account numbers. After some experimentation and about two months of evolving the tool, I developed the product later called "The 123 Ledger". This general purpose transaction processor could handle up to 1700 hundred transactions and over 100 different account numbers, all on a computer with only 256k of memory. After sifting through a years worth of comments from people using these two products, and after finally listening to Brian Heberling, a cash management specialist who insisted there was a fundamental need for a product that integrated a general ledger system with a cash flow management system, the two products were merged into a single 1-2-3 application revolving around the same transaction database. CashFlow for 1-2-3 and The 123 Ledger became, The CashFlow Ledger. I sincerely hope you find the end product of this evolutionary trail to be useful. WARNING TO BEGINNING LOTUS 1-2-3 USERS I have spent hundreds of hours teaching people how to use Lotus 1-2-3. Please keep this in mind as you read the following statements. First My experience seems to indicate that people stand to benefit far more from learning how to apply 1-2-3 themselves, than they can ever hope to benefit from using any one application of this tool. No matter how good the application is. Second Exposure to advanced macro applications such as The CashFlow Ledger too early on can, in some cases, significantly inhibit a Page 1 persons ability to learn 1-2-3 in the long term. This is because these applications give rookies the wrong impression. 1-2-3 is learnable by anyone interested in learning it. Macros are intimidating and alter the user interface, creating the wrong first impression as to the nature of the tool. With these thoughts in mind, I recommend that no beginning Lotus 1-2-3 user spend any time with this product until they understand the nature of the tool, and are thoroughly convinced that they are capable of learning and mastering 1-2-3 in a reasonable amount of time. GUIDE TO THE REST OF THIS MANUAL Preface It is here that I explain my method of distributing The CashFlow Ledger and how you can receive product updates, and notices of additional 1-2-3 enhancement products as they are developed. Chapter 2 This is a tutorial that steps you through the major functions of The CashFlow Ledger. It's a short introduction to what the product does. Chapter 3 This is a tutorial that shows you how to set up The CashFlow Ledger for your business. This chapter shows you how to use The CashFlow Ledger. Chapter 4 This is the section you refer to if the on-line help screens and tutorials do not answer your questions. It is here that I try to include everything else that might come up. Appendix A Lists all of the Alt-letter commands built into The CashFlow Ledger. It describes where they are used and for what. Appendix B Lists all range names used in The CashFlow Ledger along with cell references. Appendix C Summarizes the reporting options available in The CashFlow Ledger and includes sample printouts. Appendix D This is a tutorial teaching you how to build a simplified version of The CashFlow Ledger Summarize macro routines. This is a step towards understanding the actual macro routines being used. Appendix E This is a tutorial teaching you how to build a simplified version of The CashFlow Ledger's cash flow summary routines. This is a step towards understanding the actual macro routines being used. Appendix F This is your troubleshooting chapter. When something goes wrong and you do not understand what caused it, or how to fix it, you may refer here for a listing of the most common problems and recommended remedy. GETTING STARTED I strongly recommend that you make a backup copy of this disk immediately. Experimentation is a necessary part of learning how to use Page 2 any software product and not having a backup copy tends to discourage "what-if" learning. The first thing you need to learn about using any macro-driven 1-2-3 worksheet is how to halt the macros, and how to start them back up again. Macros are halted by pressing Ctrl-Break. You do this on an IBM keyboard by holding down the "Ctrl" key, and tapping the "Break" key (often referred to as the "Scroll Lock" key). On any other keyboard, pressing Ctrl-C will have the same affect. This will either put you in READY mode (the "mode" indicator is located in the upper right hand corner of the screen) or ERROR mode. You can always get out of ERROR mode by pressing the "ESC" key. From READY mode, you can return to The CashFlow Ledger's main menu by pressing Alt-Z. That is, hold down the "Alt" key, and tap the "Z" key. You will see this "Ctrl-Break" and "Alt-letter" notation frequently in this documentation. I encourage you to experiment with these items a bit before moving on. Another Major Barrier to Experimentation One of the truly brilliant features of Lotus 1-2-3 is the way it has you do all your work in memory. When you are done creating or modifying a worksheet, you save it to a disk file. I have noticed that the work habits of many 1-2-3 users do not take full advantage of this fact. Remember that while you are working with The CashFlow Ledger, you are only working with a copy of what is stored in the disk file. You can experiment all you want with the copy in memory, and still return to what you started with by retrieving the original file. "How do I create another copy of The CashFlow Ledger on the same disk?" you may be wondering. The answer is simply to change the file name when you save it to disk. This tells the computer to open a new folder in our electronic filing cabinet (disk), and place in it a copy of what is currently sitting on our electronic desk-top (memory). Separating the Macros From the Data You may be wondering if you have to save a new copy of the 30+k of macros each time you want to work with a new transaction database. The answer is "No you don't". The "1.5 File" options allow you to maintain transaction database files containing data only. More on the Mode Indicator The mode indicator in the upper left corner of screen is your key for determining whether or not macros are in control. Whenever a macro is be executed, the mode indicator is preceded by "CMD". The Most Useful Lotus Tip I Have to Offer Of the hundreds of 1-2-3 tips I have developed and passed on to my Page 3 students, by far the greatest time saver has to be using your left little finger (or "pinkie" as it is called in some parts of the country) to hold down the left shift key on an IBM keyboard. This converts the direction keys into ten-key mode for entering numbers. While using 1-2-3, you find yourself constantly switching between the need to enter a number and the need to point to something with the direction keys. The Num-Lock key serves the same purpose, however, while using this method, I had great difficultly remembering to un-Num-Lock. On the other hand, remembering to lift my left little "pinkie" has been no problem at all. Page 4 Chapter 2 The CashFlow Ledger Introductory Tutorial PURPOSE The purpose of this chapter is to give you a brief overview of The CashFlow Ledger's main functions and what is involved in using them. If you have any trouble with any of the following steps, you may return to the main menu at any time by pressing Ctrl-Break and then Alt-Z. GETTING STARTED Begin by booting up Lotus 1-2-3 and loading the file, "CASHLGR" with the following command: /File Retrieve <CASHLGR> <ENTER> Once the file is loaded, press any key to remove the message from the screen and call up the main menu. As you can see, there are 7 options in the main menu. Option 6 gives you access to the complete on-line help system. Option 7 is used simply for saving The CashFlow Ledger to disk. Before walking through each of these, you should note that all of The CashFlow Ledger's menus work exactly the same as 1-2-3's command menu's. The main menu may appear different at first, but if you will look at the upper left hand corner of the screen, you will notice the menu options 1-7 listed. Pressing the right arrow key moves the pointer from option to option. Pressing the ENTER key selects an option. As with 1-2-3, you can select options from any CashFlow Ledger menu either by typing the first letter, or by pointing and pressing ENTER. The first 5 options form the heart of The CashFlow Ledger. To see an overview of these options, type in the number,"6". This takes you to the on-line help directory screen which gives you an overview of the five main submenus. Along the top of your screen is a menu giving you access to help screens for each of the main menu options. You will get a chance to look at the help screens later, but for now, type the letter "Q" to return to the main menu. Everything in The CashFlow Ledger revolves around a single transaction database. The first option in the main menu is your key to modifying (adding, changing, sorting, etc.) this transaction database. Select the Modify option now by typing in the number "1". You will see the top of the transaction database appear with several sample transactions already entered. You will note that the fields (or columns) in this database are rather generic. That's right, there are only 5 - Date, Tran#, Description, Account#, and Amount. "But doesn't that greatly limit the usefulness of the product?" you may be wondering. The answer is yes it does. But on the other hand, applications you can come up with that only need these five fields (and I have good reason to believe there are several) The CashFlow Ledger handles very well. Page 5 The menu now appearing along the top of the screen is the Modify submenu. You might note that pointing to the different menu options causes a description of the option to appear on the line below. The main menu is the only CashFlow Ledger menu that uses the other technique to display these descriptions. ENTERING A TRANSACTION To add a transaction, select the Add/Edit option by typing in an "A". Notice that this simply kicks you into READY mode. Use the direction keys to move the cell pointer to column B of the next blank row in the database. Now execute the Alt-A macro by holding down the Alt key and tapping the "A" key. (From this point on I'm simply going to say "Press Alt-A" or "Press Alt-R".) To enter the date "29-Apr-85" all you have to do is type in the number 29 and press ENTER. The rest of the date will be entered for you using the current month and year displayed in the upper left corner of your screen. The following steps will complete this transaction: Type in 511 and press ENTER Type in "Magee - CashFlow Ledger" and press ENTER Type in "777" and press ENTER Type in "-75" and press ENTER That's all there is to entering a transaction. You are required only to enter the date, account#, and amount for each transaction. The rest can be skipped simply by pressing the ENTER key. Note that the Alt-A macro loops back around and positions the cell pointer for entering the next transaction. To break out of this macro (or any macro for that matter) hold down the "Ctrl" key and tap the "Break" key. The "Break" key is the same as the "Scroll Lock" key on most keyboards. From this point on, I will simply say "press Ctrl-Break". Because the Add/Edit option places you in READY mode, you are free to modify the transaction database using 1-2-3's normal commands. Several macros have also been set up for use within this option. You can take a quick look at them by pressing Alt-H. These macros will be covered as we progress through the tutorial. Press ENTER to return to the database. Press Alt-M to return to the Modify submenu. SORTING THE DATABASE Select the Sort option from the Modify submenu by typing in an "S". The screen will flash for a moment and then the Sort submenu will appear indicating the different ways the database can be sorted. To sort by account number, type in a "4". You are then asked to indicate (A)scending or (D)escending. Type in an "A" and press ENTER to indicate ascending (lowest to highest). The database will be sorted instantaneously. To resort the database by date, type in a "1". Then type in an "A" to Page 6 indicate ascending, and press ENTER. Type in a "Q" to exit the Sort menu and return to the Modify submenu. VIEWING THE MODIFY HELP SCREENS Type in an "H" to display the first Modify help screen. You can view the remaining help screens by pressing the ENTER key. After the last screen has been displayed, you will be returned to the Modify submenu. Select the Quit option from the Modify submenu by typing a "Q". The screen will flash a moment before returning you to the main menu. THE QUERY DATA OPTIONS From the main menu, type in a "2" to select the Query Data Option. The submenu that appears gives you several easy ways to selectively view transactions from the database. For example, to see all transactions for account number 655, select the Account option by typing an "A". When prompted to enter an account number, type in "655" and press ENTER. In a moment, you will see all transactions for that account number appear on the screen, along with the account's balance in the upper right corner. When finished you are returned to the Query submenu. The Date_Range option allows you to see all transactions falling between two dates. Select this option now by typing in a "D". The top portion of the screen will be updated and a new menu will appear. There are several options within The CashFlow Ledger that ask you to enter a date range. Each time you enter a date range for one of these options it is "remembered" for all subsequent "Date_Range" options. When you select any of the "Date_Range" options, this last date range will be displayed as it is on your screen now, and you will see a menu giving you the opportunity to change the dates or leave them the same. All dates in The CashFlow Ledger must be entered in numeric format. To illustrate, select the "Change_Dates" option from the menu now appearing. Enter the following responses enclosed in <> followed by an ENTER when prompted: Enter beginning year number <85> Enter beginning month number <4> Enter beginning day number <1> Enter ending year number <85> Enter ending month number <4> Enter ending day number <15> After answering the last prompt, the transactions falling between these two dates will appear on the screen along with the total in the upper right corner. When the Query submenu reappears, select the "Quit" option by typing in a "Q". Page 7 THE SUMMARIZE OPTIONS The CashFlow Ledger has two main uses: It can either be used as a posting tool or a short term cash flow management tool. In some cases, it can be used for both at the same time. To illustrate what is meant by a "posting tool", select the Summarize option from the main menu by typing in a "3". Then select the "All" option from the Summarize submenu by typing in an "A". In a moment or two, you will see a list of account numbers appear, with the total, or balance of each account to its right. The list of account numbers is taken from the transaction database itself. Every time you enter a transaction with a different account number into the database, it will be automatically added to this list the next time you summarize. The balance for each account is calculated by adding up the "Amount"s of all transactions for each account number. Note that the "Date_Range" option allows you to do the same thing but it only includes transactions falling within a specified time period. PRINTING SUMMARY REPORTS If you have a printer handy, you might try printing the following reports for your reference. From the Summarize submenu, select the "Print" option by typing in a "P". The first option, "Summary" is simply a printout of what you currently see on the screen. The "Account" option on the other hand, takes each account number shown in the summary area, prints out its balance, along with all of the transactions that make up that balance. This is often referred to as a G/L listing in the accounting world. Note that all print options in The CashFlow Ledger remind you to align the printer paper before they begin printing. You may stop the printing of any report by pressing Ctrl-Break once. After printing these reports, select the "Quit" option to return to the Summarize submenu, and "Quit" again to return to the main menu. THE CASHFLOW SUMMARY OPTIONS If your company already has a useful accounting system installed, you might consider using The CashFlow Ledger for its short term cash flow management system alone. The cash flow routines graphically summarize the amount cash increases or decreases each day, and the daily cash balance resulting from the transactions in the database for the current month. From the main menu, select the Cash Flow Summary option by typing in a "4". Select the "Calculate" option by typing in a "C". When prompted for a beginning balance, type in "100" and press ENTER. In a few moments, the daily cash flow data will be calculated and the cash flow summary shown in the upper half of the screen will be updated. When the CashFlow submenu appears, select the "Graph" option by typing in a "G". To view a line graph of the daily cash flow resulting from Page 8 April transactions in the database, type in a "C". Press any key to remove the graph from the screen. To view a line graph of April's daily balance, type in a "D". Note that you may have several months worth of data in the transaction database and still see these cash flow graphs for any one month. The "Calculate" routines use Date arithmatic to distinguish between the months. The current year and month shown in the upper left of your screen determine which month the cash flow will be calculated for. The current year and month are changed via the "Date" option. This concludes the introductory tutorial. I hope it has opened your eyes to some of the ways The CashFlow Ledger can be used to save time and money in your business. Page 9 Chapter 3 Setting up The CashFlow Ledger For Your Own Business PURPOSE The purpose of this chapter is to walk you through the following steps necessary to set up The CashFlow Ledger for your business: - Loading The CashFlow Ledger - Initializing the database - Entering transactions - Setting up recurring transactions LOADING THE CASHFLOW LEDGER Begin by retrieving the file CASHLGR from disk by entering the following command: /File Retrieve <CASHLGR> <ENTER> (Note: The <>'s need not be entered) INITIALIZING THE CASHFLOW LEDGER When loaded, press ENTER once to remove the Copyright notice from the screen and call up the main menu. Initializing The CashFlow Ledger removes all transactions from the database. You do this with the "Setup" option in the main menu. Select this now by typing in a "5". Notice that the main menu descriptions remain on the screen but that a new menu appears at the top of the screen. Select the "Init" option from this menu now by typing in an "I". You are given one last chance to cancel the command in case it was selected accidently. You want to select the "Yes" option to complete the command by typing a "Y". The screen will flash for a moment and then you will be asked to enter the first transaction. This is necessary to keep the macros happy. When you go to enter transactions from within the Add/Edit option, you will only be required to enter the day of the month for the "Date" column. When entering this first transaction however, you are asked to enter the year, month, and day. The year and month are used to establish the "current year and month". The current year and month can be changed later using any of the date options. Go ahead and enter the first transaction as prompted. If you don't have it handy, just enter a dummy transaction for now, you will have a chance to edit or replace it later. When finished, you will be returned to the Setup submenu. At this point, you have done everything necessary to begin adding your own transactions to the database. We will return to the Setup option later, but for now, type in a "Q" to return to the main menu. Page 10 IS IT CASH FLOW OR LEDGER? The CashFlow Ledger can be used in two completely different ways. First, to use it as a short term cash flow management system, you enter transactions you expect to occur in the future into the transaction database. With this done, you can evaluate the cash flow that will result from this activity using the Cashflow Summary options. Second, to use it as a ledger management system, you enter historical transactions and then use the Summarize options to calculate the balances of each account. How about both at the same time? Both future and historical transactions can be maintained in database at the same time. The CashFlow Ledger simply looks at the "Date" column in the database to distinguish between the two. As time passes, there is no reason why future transactions (if they indeed actually occur as expected) can't be used for ledger purposes as well. MAINTAINING MULTIPLE LEDGERS You may decide that you would like to maintain multiple ledgers. The "1.5 File" option is your key to doing this. This option allows you to extract the transaction database to a separate file or retrieve a transaction database already stored in a separate file. This way, you need not store a separate set of CashFlow Ledger macros for each transaction database you wish to maintain. If you need to combine more than one transaction database, I recommend that you use 1-2-3's /File Combine Copy command while in Add/Edit mode. Just make sure you have positioned the cell pointer to where you want the transactions from the new database to begin, before entering the command. Because it is sometimes difficult to remember to enter checks as negative numbers, you may want to maintain separate ledgers for checks and deposits, and then merge them after they have all been entered. ENTERING TRANSACTIONS To begin entering transactions, select the "Modify" option from the main menu by typing a "1", and then the "Add/Edit" option from the Modify submenu by typing an "A". Notice that this option kicks you out of macro control and into READY mode (as indicated in the upper right corner of your screen). In READY mode, you have full access to 1-2-3's regular commands. For example, if you entered a dummy transaction at the time you initialized The CashFlow Ledger, you can easily erase it now with the /Range Erase command. The two commands you should avoid using if possible are the /Worksheet Delete Row and /Move. These are the two commands that destroy range names if you are not careful. If you do need to use them for some reason, just be sure that you do not delete or move another range over the top of one of the end points in the named range, DB. You may want to check the coordinates of this range before entering a Delete Row or Move command. Page 11 The Current Year And Month Before you begin entering transactions First make sure that the "current year and month" in the upper left corner of your screen refer to the month you want to analyze. This date can either be changed by selecting the "Date" option from the Modify submenu, or by using the Alt-D macro from within the "Add/Edit" option. Go ahead and try this now by holding down the "Alt" key and tapping the "D" key. Enter the year and month numbers as prompted and press the ENTER key after typing in each response. The Alt-A Macro To begin adding transactions, simply move the cell pointer using the direction keys to the next blank row in the database. Starting in the "Date" column, execute the Alt-A macro by holding down the "Alt" key and tapping the "A" key. To enter the date, all you have to type is the day number. The rest of the date will be filled in using the current month and year shown in the upper left hand corner of the screen. The "Tran#" and "Description" columns are optional and can be skipped by pressing the ENTER key. After entering the amount, the macro positions the cell pointer for entering the next transaction. To stop the Alt-A macro, press Control Break by holding down the "Ctrl" key and tapping the "Break" key (or "Scroll Lock" key on some keyboards). Other Alt-Letter Commands To see a list of all Alt-letter commands available from within the "Add/Edit" option, hold down the "Alt" key and tap the "H" key. To return to the transaction database, simply press the ENTER key. Alt-S and Alt-Y are your shortcuts to the summarize options. Alt-C takes you directly to the Cash Flow Summary routines. Alt-L and Alt-R are the keys to entering recurring transactions which we will go over shortly. Recalculation Method The more transactions you enter, the longer it takes 1-2-3 to recalculate the worksheet. Just use 1-2-3's /Worksheet Global Recalculation command to switch over to Manual recalculation. The worksheet can be recalculated any time you wish by pressing the F9 key. All macros function properly in with recalculation set either to Manual or Automatic. SETTING UP RECURRING TRANSACTIONS (IT IS NOT RECOMMENDED THAT YOU ATTEMPT TO USE THIS RECURRING TRANSACTION FEATURE UNTIL YOU: ARE THOROUGHLY FAMILIAR WITH 1-2-3'S RANGE NAME COMMANDS, ARE COMFORTABLE ISSUING ALT-LETTER MACRO COMMANDS, AND KNOW HOW TO PRESS CONTROL-BREAK TO "UNDO" A COMMAND YOU ARE IN THE PROCESS OF ENTERING. THIS FEATURE HAS BEEN INCLUDED BECAUSE IT HAS SO MUCH POTENTIAL USEFULNESS, I BELIEVE IT IS WORTH INVESTING THE TIME TO Page 12 LEARN THE ABOVE ITEMS.) Recurring transactions are set up from within the "Setup" main menu option. Press Alt-M to return to the Modify submenu, and then select the "Quit" option to return to the main menu. Select the "Setup" option by typing a "5". And finally, select the "Recur_Trans" option by typing an "R". Now select the "Add/Edit" option from this menu by typing an "A". You will note that this "Add/Edit" option is similar to the Modify Add/Edit option. It also kicks you into READY mode and also has some Alt-letter commands set up to assist you. The idea behind the recurring transaction feature is that most businesses have transactions or groups of transactions that occur every month. (i.e. payment of rent, payments to suppliers, payments on bank notes, depreciation, bank service charges ..). You can set these transactions up in this section once, and then copy them into the database each month with just a couple keystrokes. The usefulness of this feature will be greatest if you follow these instructions very carefully when setting up the transactions. Recurring transactions are set up as follows: 1) Enter a one line description of the transaction group in column A. This description must be entered as a Label and begin with the "*" character. The "*" flags it for inclusion in the listing of recurring transaction descriptions produced by the Alt-L macro. It's a good idea to include in this description, the named range to be assigned to the transaction group. The named range is what you will be expected to remember when it comes time to add a group of recurring transactions to the database via the Alt-R macro. 2) Use the Alt-T macro to enter the transactions as you would like them copied into the transaction database each time they are to be used. Alt-T enters the date in a special format that ties it to the current month and year. The other macros function the same as Alt-A. To complete the date, you need only type in the day number and press ENTER to continue on. 3) Use 1-2-3's /Range Name Create command to assign a name to the entire group of transactions. You must be careful here not to use a range name currently in use by The CashFlow Ledger's macros. If when you enter the range name and press return, the cell pointer jumps to some other location in the worksheet, halt the operation immediately by pressing Ctrl-Break. I recommend the use of numbers for names. In other words, assign the first group of transactions the name "1", the second, "2", and so on. A couple of sample recurring transactions have been set up to give you a better idea of what is involved. The range names 1, and 2 have been assigned to these two blocks of transactions. To verify this, enter the following command: /Range Name Create <1> <ENTER> (Do not enter <>'s) After entering this command, you should see the first block of Page 13 transactions highlighted. This is what is copied into the database when you enter recurring transaction number 1 using the Alt-R option. Note also the description beginning in column A. It begins with the letter "*" and includes the range name assigned to the transactions below it. The "*" is required for the Alt-L macro to work. The range name is included in this description only to remind you what it is. One last thing you should note is how the dates have been entered. You will learn how to enter the recurring transactions in a moment. For now, just note that by entering the dates in this fashion, it ties them to the "Current year and month". To see how they are entered, we need to return to the database using the following steps: Press Alt-N Select the Quit option Select the Quit option again Select option "1" from the main menu Select the Add/Edit option Now move the cell pointer down a few lines past the last transaction and into column A. To see a list of the recurring transactions currently set up, press Alt-L. In a moment, you should see the two lines appear describing each of the recurring transactions. After noting the range names assigned to each, (in this case the numbers "1" and "2"), use 1-2-3's /Range Erase command to erase these two descriptions and the "*" that appeared above them. Move the cell pointer to column B of the next blank row and press Alt-R. You will be asked to enter a range name, type in either a "1" or a "2" and press ENTER. In a moment, you will see one of the blocks of transactions copied in, and each of the dates converted from formulas to their current values. This is done to prevent the dates from changing each time you change the "Current Year and Month". You may want to erase these from your database with the /Range Erase command before going on to set up your own recurring transactions. Now return to the Recurring transaction section with the following steps: Press Alt-M Select the Quit option Select option "5" from the main menu Select the Recur_Trans option Select the Add/Edit option You will probably want to use the /Range Erase command to erase the two sample recurring transactions. Notice that erasing them does not have Page 14 any affect on the range names. You must remove these with the Range Name Delete command. Please be extremely careful here. Move to column A of the row to contain your first recurring transaction. Enter a description of the first block of recurring transactions. Make sure that the first character in this description is an asterisk "*". With the description entered, you are now ready to enter the transactions. Move to column B of the row following the description and press Alt-T. You will see "@DATE($YEAR,$MONTH," appear on the command line. Type in the day of the month you expect the first transaction to occur and press ENTER. The transaction number will be different each month so just press ENTER to move on to the Description column. Type in the description and press ENTER. Type in the Account number and press ENTER. Type in the amount and press ENTER. The cell-pointer will be positioned for entering the next transaction in the group. Enter each of the transactions you would like included in this block. When finished, press Control-Break to exit the Alt-T macro. Then use the /Range Name Create command to assign a name to the block of transactions. Be sure and include all columns (Date through Amount) in the named range. The range name you use here is the answer you will give the Alt-R macro. It does not have to be a number. I just used numbers to illustrate one approach. Again, if you don't use numbers, you run the risk of entering a name already in use by The CashFlow Ledger's macro routines. If you enter a name already in use, it will highlight the range previously assigned the name. If this occurs, simply press Control-Break to halt the command. Repeat the above process for any other "groups" of recurring transactions you need to set up, and then return to "1.1 Add/Edit" to try them out. You should try changing the current date with the "1.4 Date" option to see how the dates in these recurring transactions automatically adjust. Page 15 Chapter 4 Reference Section PURPOSE The purpose of this chapter is indicate the purpose of each command and the action required to execute it. You should refer to the appropriate section of this manual when the on-line help screens and tutorials prove to be of no assistance. 1 Modify Options Purpose: Use this option any time you want to modify the transaction database. Menu Structure: 1.1 Add/Edit 1.2 Sort 1.3 Balance 1.4 Date 1.5 Print 1.6 File 1.6.1 Xtract 1.6.2 Retrieve 1.6.3 Quit 1.7 Help 1.8 Quit 1.1 Add/Edit Purpose: Use to add new transactions or edit existing transactions. Action: Since selecting this option puts you in READY mode, you are free to move around using the regular direction keys. You may enter or modify transactions using 1-2-3 commands or by using the following macros: Alt-A Use to add transactions to the database. Requires that you be in column B before it is executed. When executed, you are prompted for the following information: Day # - The current month and year displayed in the upper left corner of the screen are combined automatically with this day number to enter the complete date of the transaction. The current month and year can be changed by selecting any of the "Date" options appearing in the various menus, or by using the Alt-D macro while in Add/Edit mode. Tran# - Since this field is not used in any of the calculations, you have total flexibility over how it is used. You may enter check Page 16 numbers, journal entry numbers, or simply ******'s if for example, you are entering "future" transactions for cash flow projection purposes, where the check numbers are not yet known. It is not a required entry and may be skipped by simply pressing the ENTER key. Descr - A description of the transaction which may be up to 240 characters long. Only 28 characters of this column will be displayed due to the columns width, but it will still be retained and can be viewed by editing the cell. Again, this column is not used in any of the calculations and therefore be skipped simply by pressing the ENTER key. Acct# - An account number by which you would like to total a group of transactions. You must either develop your own chart of accounts or use one of the sample chart of accounts provided on the disk. In either case, it is essential that you enter the transactions in accordance with your chart of accounts consistently. Amount- You decide how you want to enter transaction amounts. There are many possible methods, but once established, the method should be followed consistently. If for example, you wish to use The CashFlow Ledger as a General Ledger system, debits could be entered as positive numbers and credits as negative. This way, you could always tell if you were in balance by checking the "Balance" in the upper right corner of the screen. If debits equal credits, this "Balance" should equal 0. If on the other hand, you decide to use The CashFlow Ledger as a checkbook ledger, then deposits could be entered as positive numbers and checks as negative numbers. In this case, the "Balance" will indicate your checkbook balance and the 1.3 Balance option could be used to view your balance on any previous date. The CashFlow Ledger is used frequently as simply a transaction summarizer. If you decide to go this way, you might consider entering all deposits in one file and all checks in another to avoid having to worry about entering positive and negative numbers. Alt-C Use as a short cut to jump you automatically to the "4.1 Calculate" routine. Prompts you for a beginning balance performs the calculations, and returns you to the Cash Flow Summary submenu. Alt-D Use to change the current month and year for transaction entry. Should be used only while in Page 17 Add/Edit mode. Alt-H Takes you to a help screen listing information related to the Add/Edit option. Pressing the ENTER key returns you to the Add/Edit option. Alt-L Use to list descriptions of all recurring transactions you have set up using the "5.1 Recur_Trans" option. You must follow the rules carefully when setting up these recurring transactions for the Alt-L macro to work. Be sure and move to a blank area before executing this macro to avoid having the listing wipe out data. Use 1-2-3's Range Erase command to remove the listing after viewing. Alt-M Returns you to the Modify submenu. Alt-R Use to copy in a set of recurring transactions as entered using the "5.1 Recur_Trans" option. Prompts you for a the range name assigned to the set of transactions, then copies in the named range, and converts the dates from @DATE formulas to numbers. This last step prevents the dates from changing when the current month and year are changed. This macro should be executed while in column B if the recurring transactions have been set up exactly as instructed. Alt-S Use as a short cut to the Summarize submenu while in READY mode. Alt-Y Use as a short cut to the "3.1 All" menu option while in READY mode. This macro was included to maximize the "what-if" capabilities of the application. Notes: Remember that the F10 (Graph) key can be used to display the last graph shown on the screen and that the F8 (Table) key can be used to recalculate the last Data Table command. Which Data Table command it executes depends on the last summary you performed via the macros was by account number or daily cash flow. 1.2 Sort Purpose: Use to sort the transaction database by any of the 5 columns in either Ascending or Descending order. Not recommended if you have used formulas while entering any of the transactions. Will not work if you have skipped lines in the database. Action: After selecting this option, the screen will flash for a moment, and then the following menu options will appear Date Tran# Descr Acct# Amount Quit After selecting one of them, type in an A or D and then press ENTER to complete the sort. 1.3 Balance Page 18 Purpose: Use to view the total of all transactions up to and including a specific date. If you are using The CashFlow Ledger as a checkbook ledger, this option can be used to determine you balance on a specific date thus eliminating the need to maintain a separate column for a running balance. Action: You must enter the year, month, and day in numeric format as prompted. After the balance is displayed, a message appears on the screen indicating that you must press ENTER to return to the Modify submenu. 1.4 Date Purpose: Use to change the current month and date for transaction entry. Has the same affect as the menu options; "4.3 Date" and "5.2 Date" Action: You must enter the year and month in numeric format as prompted. 1.5 Print Purpose: Use to print a raw listing of the entire transaction database in its current sorted order. Appendix C contains a sample listing of this report and more information on printing. Action: After selecting this option, you will be prompted to align the printer paper and then press ENTER. After printing, you will be returned to the Modify submenu. 1.6 File Purpose: Use to maintain database files separate from The CashFlow Ledger's macro routines. 1.6.1 Xtract Purpose: Use to store transactions in files separate from The CashFlow Ledger's macro routines and work areas. Action: After selecting this option, you will be prompted for a file name. You type in the name and press ENTER. A message will then appear on the screen indicating that after the save has been completed, you are to press Alt-M to return to the main menu. If a file already exists with the name you entered, you will be given a chance to cancel the Xtract command, or replace the old file with the new set of transactions. 1.6.2 Retrieve Purpose: Use to erase transactions currently in the database and retrieve transactions from another file. It is recommended that you use this option only to retrieve transactions from files created by the 1.6.1 Xtract command. Action: After selecting this option, you will be given one last chance to cancel the command. This serves to warn you that all Page 19 transactions currently in the database will be erased. If you select the "No" option, you will be returned to the Modify submenu without affecting the database. After selecting the "Yes" option, the current transactions database will be erased and you will be prompted for the name of the file to combine. A list of all file names in the current directory will be displayed on the control panel for you to select from. After selecting a file name, the transactions will be added to the database and you will be returned to the Modify submenu. 1.6.3 Quit Purpose: Use to return to the Modify submenu. 1.7 Help Purpose: Use to display the help screens for the "Modify Database" menu options. Action: After selecting this option, simply press the ENTER key until you have been returned to the Modify submenu. 1.8 Quit Purpose: Use to return to the main menu. Note: In some cases, you will enter commands while within the "Add/Edit" option that screw up the key range name, "DB". This is the "Achilles heel" of The CashFlow Ledger. If, when you select the "Quit" option from the Modify submenu, you get an error message indicating the range name "DB" does not exist or is invalid, you will need to do the following: - Press the "Esc" key to clear the error. - Enter the command: /Range Name Create <DB> <ENTER> When it asks for a range, use the direction keys to highlight the entire transaction database, including the field names (Date, Tran#, Descr, etc.). The reason this is necessary is because when you select this "Quit" option, the macros go through the following steps: - Clear titles and move cell pointer to upper left corner of the named range, "DB". This should place the cell pointer in cell containing the field name, "Date". - Deletes the range name, "DB". - Re-creates the range name, "DB", and assigns it a range encompassing the entire transaction database. 2 Query Options Purpose: Use to view selected transactions from the database. Menu Structure: Page 20 2.1 Account 2.2 Date_Range 2.3 Both 2.4 Freeform 2.5 Scan 2.6 Print 2.7 Help 2.8 Quit 2.1 Account Purpose: Use to view all transactions in the database for a given account number. Also displays the total of all transactions for the account number in the upper right corner of the screen. Action: After selecting the option, type in the account number and press the ENTER key. 2.2 Date_Range Purpose: Use to view all transactions in the database for a given time period. Action: After selecting this option, new headings will appear on the screen indicating the current date range. A menu will appear that lets you use the current date range or enter a new one. (Remember that date ranges are always inclusive.) If you elect to change the date range, you will be asked to enter the year, month, and day in numeric format for both the beginning and ending dates. This date range will be remembered the next time you do any operation that requires a date range to be entered. 2.3 Both Purpose: Use to view all transactions entered in the database for a given account number for a given time period. (Example: to display all transactions for account number 777 for the month of May.) Action: After selecting this option, new headings will appear on the screen indicating the current date range. A menu will appear that lets you use the current date range or enter a new one. (Remember that date ranges are always inclusive.) If you elect to change the date range, you will be asked to enter the year, month, and day in numeric format for both the beginning and ending dates. This date range will be remembered the next time you do any operation that requires a date range to be entered. After selecting an option from this date menu, you will be asked to enter and account number. 2.4 Freeform Purpose: Use to view selected transactions from the database based on your own criteria. Should be used by experienced users only, and even they should save their work to disk before messing with this one. Page 21 Action: All Data Query Extract parameters have been predefined so that all you have to do is enter the criteria under the appropriate field name and press the Query key (F7). Can also be used to delete a selected group of transactions from the database by entering the /Data Query Delete command after setting up the appropriate criteria. (Caution is advised). Press Alt-Q to return to the Query submenu. 2.5 Scan Purpose: Use to move cell pointer in order to view transactions that do not fit on the screen after an extract. Action: After selecting this option, you will be free to move around the cell pointer. To return to the Query submenu, simply press the ENTER key as indicated on the screen. 2.6 Print Purpose: Use to print the current contents of the Query section (the results of the latest Query Extract). Action: After selecting this option, align the printer paper and press ENTER as instructed. See Appendix C for a sample printout of this report. 2.7 Help Purpose: Use to view the on-line help screens for the Query menu options. Action: After selecting this option, simply press the ENTER key until you have been returned to the Query submenu. 2.8 Quit Purpose: Use to return to the main menu. 3 Summarize Options Purpose: Use to summarize transactions by account number. Menu Structure: 3.1 All 3.2 Date_Range 3.3 Xtract 3.4 Scan 3.5 Print 3.5.1 Summary 3.5.2 Account 3.5.3 Date_Range 3.5.4 Quit 3.6 Help 3.7 Quit 3.1 All Purpose: Use to summarize all transactions in the database by account Page 22 number. Creates a list of account numbers based on the account numbers used in the transactions. Every unique account number entered in the database is included in the list. After obtaining this list, the total of all transaction amounts for each account number is calculated and displayed to its right. The total of all transactions in the database is displayed in the upper right hand corner of the screen. Action: After selecting this option, wait a few moments, the calculations will be completed automatically, and you will be returned to the Summarize submenu. 3.2 Date_Range Purpose: Use to summarize all transactions in the database by account number for a specified time period. This allows you to enter transactions for multiple time periods into a single database and still obtain individual summaries. Creates a list of account numbers based on the account numbers used in the transactions. Every unique account number entered in the database is included in the list. After obtaining this list, the total of all transaction amounts for each account number, for the specified date range, is calculated and displayed to its right. The total of all transactions in the database is displayed in the upper right hand corner of the screen. Action: After selecting this option, a date range will appear on the right side of the screen. A menu will appear giving the option of either changing them or proceeding with them as is. If you elect to change these dates, you will have to enter beginning and ending years, months, and days, in numeric format. After deciding which way to go with the date range, the calculations will be performed automatically, and you will be returned to the Summarize submenu. 3.3 Xtract Purpose: Extracts the results of the last summarize operation to a separate worksheet file for use by a subsequent worksheet, such as a P&L report. The design of the P&L has been left up to you, however, you will find a sample P&L included on the disk to give you some ideas on how to build your own. Action: After selecting this option, the macros will suggest a file name that corresponds with the current month. You have the option of editing or overriding this name. Press the ENTER key after typing in the desired file name. You will not be given a chance to cancel the command if a file exists with the same name. 3.4 Scan Purpose: To activate the direction keys allowing you to move the cell pointer around and thus view any summary information that doesn't fit on the screen. Action: After selecting this option, the direction keys will be activated until the ENTER key is pressed. Pressing ENTER returns you to the Summarize submenu. Page 23 Notes: If you wish to modify the summary data in any way, you will need to break out of the macros by pressing the BREAK key. This is done by holding down the "Ctrl" key and tapping the "Scroll Lock" key. After doing this, press Alt-Z to return to the macro menus. 3.5 Print Purpose: Calls up a submenu allowing you to print various summary reports. See Appendix C for more information on Printing Reports. 3.5.1 Summary Purpose: Prints the current contents of the summary section. Action: After selecting this option, align printer paper and press the ENTER key to start the printing process. 3.5.2 Account Purpose: To produce a report that includes each account numbers balance and a listing of the transactions that make up that balance. "Commonly referred to as a G/L listing". See Appendix C for a sample printout of this report. Action: After selecting this option, align printer paper and press the ENTER key to start the printing process. 3.5.3 Date_Range Purpose: To produce a report that includes each account number's balance and a listing of the transactions that make up that balance for a specified time period. Accounts with no transactions for the time period are not included in this report. Commonly referred to as a G/L listing. See Appendix C for a sample printout of this report. Action: After selecting this option, a date range will be displayed on the screen and a menu will be displayed allowing you to use them as is, or change them. If you leave them unchanged, you will be asked to align printer paper and press the ENTER key to start the printing process. If you elect to change them, you must enter both the beginning and ending year, month, and day in numeric format. After completing this, align the printer paper and press the ENTER key as indicated. 3.5.4 Quit Purpose: Returns you to the Summarize submenu. 3.6 Help Purpose: Use to display the help screens for the "Summarize Database" menu options. Action: After selecting this option, simply press the ENTER key until you have been returned to the Summarize submenu. Page 24 3.7 Quit Purpose: Returns you to the main menu. 4 Cash Flow Summary Options Purpose: Use to produce cash flow and daily balance summaries in both printed and graphic form. Menu Structure: 4.1 Calculate 4.2 Graph 4.2.1 Cash_Flow 4.2.2 Daily_Balance 4.2.3 Save 4.2.4 Quit 4.3 Date 4.4 Scan 4.5 Print 4.6 Help 4.7 Quit 4.1 Calculate Purpose: Use to calculate the daily cash flow and daily balance figures upon which the graphs are based. Calculations are performed for the current month, which is indicated in the upper right hand corner of the screen. The current month can be changed from the "4.3 Date" option. Action: After selecting this option, you will be prompted for a beginning balance. Enter a 0 if this has already been entered in the database as a transaction. These calculations are complex and can take from 15 seconds to several minutes depending on the number of transactions entered in the database. 4.2 Graph Purpose: Use to graphically summarize the daily cash flow and balance figures produced by the "4.1 Calculate" option. 4.2.1 Cash_Flow Purpose: Use to view a line graph of the daily cash flow for the current month. Action: Graph appears almost instantly after selecting the option. Press any key to remove the graph from the screen and return to the Graph submenu. Notes: Macros call up the named graph settings "CASHFLOW". 4.2.2 Daily_Balance Purpose: Use to view a line graph of the daily cash balance for the current month. Page 25 Action: Graph appears almost instantly after selecting the option. Press any key to remove the graph from the screen and return to the Graph submenu. Notes: Macros call up the named graph settings "BALANCE". 4.2.3 Save Purpose: Saves a picture of the last graph displayed on the screen to a PIC file for printing by the 1-2-3 PrintGraph program. Action: After selecting this option, enter a file name and press ENTER. After the save, you will be returned to the Graph submenu. 4.2.4 Quit Purpose: Returns you to the Cash Flow submenu. 4.3 Date Purpose: Use to change the current month and date for which to calculate the cash flow and balance figures. Has the same affect as the menu options; "1.4 Date" and "5.2 Date" Action: You must enter the year and month in numeric format as prompted. 4.4 Scan Purpose: To activate the direction keys allowing you to move the cell pointer around and thus view any cash flow information that doesn't fit on the screen. Action: After selecting this option, the direction keys will be activated until the ENTER key is pressed. Pressing ENTER returns you to the Cash Flow submenu. 4.5 Print Purpose: Prints a copy of the Cash Flow Summary section. Action: After selecting this option, simply align printer paper and press the ENTER key as instructed. See Appendix C for a sample printout of this report. 4.6 Help Purpose: Use to display the help screens for the "Cash Flow Summary" menu options. Action: After selecting this option, simply press the ENTER key until you have been returned to the Cash Flow Summary submenu. 4.7 Quit Purpose: Returns you to the main menu 5 Setup Options Page 26 Purpose: Use these options to set up The CashFlow Ledger for your business. Menu Structure: 5.1 Recur_Trans 5.1.1 Add/Edit 5.1.2 Print 5.1.3 Help 5.1.4 Quit 5.2 Date 5.3 Init 5.4 Formfix 5.5 EraseHelp 5.6 X/C 5.6.1 Expand_Query 5.6.2 Expand_Summary 5.6.3 Contract_Query 5.6.4 Contract_Summary 5.6.5 Quit 5.7 Help 5.8 Quit 5.1 Recur_Trans Purpose: Use to set up transactions that are entered repeatedly during the month or between months. 5.1.1 Add/Edit Purpose: Use to set up the Recurring transactions so they can be easily incorporated into the transaction database when needed. Action: Recurring transactions are set up in groups as follows: 1) Enter a description of the transaction group in column A. This description must be entered as a Label and begin with the "*" character. The "*" flags it for inclusion in the listing of recurring transaction descriptions produced by the Alt-L macro. It's a good idea to include in this description the named range to be assigned to the transaction group. The named range is what you will be expected to remember when it comes time to add a group of recurring transactions to the database. 2) Use the Alt-T macro to enter the transactions as you would like them copied into the transaction database each time they are to be used. Alt-T enters the date in a special format that ties it to the current month and year. The other macros function the same as Alt-A. 3) Use 1-2-3's /Range Name Create command to assign a name to the entire group of transactions. You must be careful here not to use a range name currently in use by The CashFlow Ledger's macros. If when you enter the range name and press return, the cell pointer jumps to some other location in the worksheet, halt the operation immediately by pressing Ctrl-Break. We recommend the use of numbers for names. In other words, assign the first group of transactions the name "1", the second, "2", and Page 27 so on. Once you have done this for each group of recurring transactions, press Alt-N to return to the Recur_Trans menu. 5.1.2 Print Purpose: Use to print a listing of the recurring transaction section. Action: After selecting this option, align printer paper and press the ENTER key. 5.1.3 Help Purpose: Use to display the help screens for the "Recur_Trans" menu options. Action: After selecting this option, simply press the ENTER key until you have been returned to the Recur_Trans submenu. 5.1.4 Quit Purpose: Returns you to Setup submenu 5.2 Date Purpose: Use to change the current month and date for either transaction entry or cash flow summarization purposes. Has the same affect as the menu options; "1.4 Date" and "4.3 Date". Action: You must enter the year and month in numeric format as prompted. 5.3 Init Purpose: Use to initialize all data areas in the application. Deletes all transactions in database and erases all data in the Query, Summary, and Cash Flow Summary sections. Recurring transactions are not erased. To remove them, use 1-2-3's /Range Erase command while within the "5.1.1 Add/Edit option" and delete the associated range name with 1-2-3's /Range Name Delete command. Action: After selecting this option, you will be given one last chance to cancel it. If you do not, the data will be erased and you will be asked to enter the first transaction in the database before returning to the Setup submenu. The first transaction is necessary for the macros to work correctly. 5.4 Formfix Purpose: Occasionally, while working with this application, you may do things which disrupt some of the formulas causing them to display "ERR". If this ever happens, select this option and they will be restored automatically. This process only takes a few seconds because most of the formulas used in The CashFlow Ledger are stored as labels, and converted to Page 28 formulas only when needed. This also saves memory and speeds up recalculation time. Action: Just select the option and wait a moment. 5.5 EraseHelp Purpose: Use to erase the on-line help screens from the copy of The CashFlow Ledger currently in memory. Saves approximately 15k of memory and 15k of disk space when the application is saved to disk. See option "6.7 Print" for information on printing these help screens. Action: After selecting this option, you are given one last chance to cancel the command. Select No to prevent the screens from being erased, or Yes to go ahead and erase them. 5.6 X/C Purpose: Displays a submenu of options for expanding or contracting the Query and Summary sections. The size of these sections determines the number of transactions you may enter for an account number and the number of account numbers you may use in the transaction database. 5.6.1 Expand_Query Purpose: Use to expand the area for extracting selected records from the transaction database by 10 rows. The more room you make here, the more transactions you may enter for a single account number. Having enough space here is not a requirement for the summarize options "3.1 All" or "3.2 Date_Range" to work correctly. However, the printing options, "3.5.2 Account" and "3.5.3 Date_Range" use this area when printing the transactions that make up each accounts balance. Action: Simply select the option. Everything including range names are adjusted automatically. 5.6.2 Expand_Summary Purpose: Use to expand by 10 rows, the area used for summarizing transactions by account number. The more room you make here, the more account numbers you can summarize by. As shipped, The CashFlow Ledger can summarize by up to 50 account numbers. In other words, there are 50 rows in the Summary section for listing this information. Action: Simply select the option. Everything including range names are adjusted automatically. 5.6.3 Contract_Query Purpose: Use to contract the area for extracting selected records from the transaction database by 10 rows. The less room you make here, the fewer transactions you may enter for a single account number. Having enough space here is not a requirement for the summarize options "3.1 All" or "3.2 Date_Range" to work correctly. However, the printing options, "3.5.2 Account" and "3.5.3 Date_Range" use this area when printing Page 29 the transactions that make up each accounts balance. Action: Simply select the option. Everything including range names are adjusted automatically. 5.6.4 Contract_Summary Purpose: Use to delete 10 rows out of the area used for summarizing transactions by account number. The less room you make here, the fewer account numbers you can summarize by. As shipped, The CashFlow Ledger can summarize by up to 50 account numbers. In other words, there are 50 rows in the Summary section for listing this information. Action: Simply select the option. Everything including range names are adjusted automatically. 5.6.5 Quit Purpose: Returns you to the Setup submenu 5.7 Help Purpose: Use to display the help screens for the "Setup" menu options. Action: After selecting this option, simply press the ENTER key until you have been returned to the Setup submenu. 5.8 Quit Purpose: Returns you to the main menu. 6 On-line Help Purpose: To give you quick answers to the most common questions that arise while using each of the options. Each major menu option has it's own set of help screens which can be accessed via this menu option, or by selecting the "Help" option that appears in each of the menus. To continue through the help screens simply press the ENTER key as instructed. If for some reason the macros are de-activated, hold down the "Alt" key and tap the "Z" key to return to the main CashFlow Ledger menu. The "6.7 Print" option has been set up for the curious who would like to see a complete printout of the 14 help screens. The "5.5 EraseHelp" option has been set up for those who have mastered the use of this application and no longer need them resident in memory. 7 Save Options Purpose: Use to save a copy of The CashFlow Ledger as it currently exists in memory, to a disk file or to change the directory to which the file will be saved. Menu Structure: 7.1 Save 7.2 Directory 7.3 Quit Page 30 7.1 Save Purpose: Use to save your work to disk for permanent storage. Remember that you can create new versions of The CashFlow Ledger simply by saving it to a different file name. Also remember that when you load The CashFlow Ledger into memory, you are only working with a copy what was loaded from the disk. You do not change the version sitting on the disk until you save the application back to disk using the same file name. Understanding this fact is an important step in learning to experiment with The CashFlow Ledger. Note that this option saves both data and macros to a file whereas the "1.5.1 Xtract" command saves only the data to the file. Action: After selecting the option, a message will appear on the screen reminding you to press Alt-Z to return to the main menu AFTER the file save process has been completed. You press ENTER once to acknowledge, the message will disapear, and the original file name will be displayed as the save name. To use it again, simply press the ENTER key. You will be given a chance to Cancel or Replace if the file name already exists on the disk in the default drive. To create a new file, just type in a new file name over the old one and press ENTER. If you happen to type in a name already in use, you will be given a chance to "Cancel" the command, or "Replace" the old file with the new. 7.2 Directory Purpose: Use to change the directory to which the file will be saved. Action: After selecting this option, simply type in the new directory over the old and press the ENTER key. 7.3 Quit Purpose: Returns you to the main menu. Page 31 Appendix A Listing of Alt-Letter Macro Commands PURPOSE To serve as a quick reference guide to the various Alt-letter commands available throughout The CashFlow Ledger. NOTE You must be in READY mode, as indicated in the upper right hand corner of your screen, before executing any of these macros. TO EXECUTE To execute one of these macros, hold down the "Alt" key and tap the letter key indicated. THE MACRO COMMANDS Alt-A Use to add transactions to the database. Requires that you be in column B before it is executed. When executed, you are prompted for the following information: Day # - The current month and year displayed in the upper left corner of the screen are combined automatically with this day number to enter the complete date of the transaction. The current month and year can be changed by selecting any of the "Date" options appearing in the various menus, or by using the Alt-D macro while in Add/Edit mode. Tran# - Since this field is not used in any of the calculations, you have total flexibility over how it is used. You may enter check numbers, journal entry numbers, or simply ******'s if for example, you are entering "future" transactions for cash flow projection purposes, where the check numbers are not yet known. It is not a required entry and may be skipped by simply pressing the ENTER key. Descr - A description of the transaction which may be up to 240 characters long. Only 28 characters of this column will be displayed due to the columns width, but it will still be retained and can be viewed by editing the cell. Again, this column is not used in any of the calculations and therefore be skipped simply by pressing the ENTER key. Acct# - An account number by which you would like to total a group of transactions. You must either develop your own chart of accounts or use one of the sample chart of accounts provided on the disk. In either case, it is essential that you enter the transactions in accordance Page 32 with your chart of accounts consistently. Amount- You decide how you want to enter transaction amounts. There are many possible methods, but once established, the method should be followed consistently. If for example, you wish to use The CashFlow Ledger as a General Ledger system, debits could be entered as positive numbers and credits as negative. This way, you could always tell if you were in balance by checking the "Balance" in the upper right corner of the screen. If debits equal credits, this "Balance" should equal 0. If on the other hand, you decide to use The CashFlow Ledger as a checkbook ledger, then deposits could be entered as positive numbers and checks as negative numbers. In this case, the "Balance" will indicate your checkbook balance and the 1.3 Balance option could be used to view your balance on any previous date. The CashFlow Ledger is used frequently as simply a transaction summarizer. If you decide to go this way, you might consider entering all deposits in one file and all checks in another to avoid having to worry about entering positive and negative numbers. Alt-C Use as a short cut to jump you automatically to the "4.1 Calculate" routine. Prompts you for a beginning balance performs the calculations, and returns you to the Cash Flow Summary submenu. Alt-D Use to change the current month and year for transaction entry. Should be used only while in Add/Edit mode. Alt-H Takes you to a help screen listing information related to the Add/Edit option. Pressing the ENTER key returns you to the Add/Edit option. Alt-L Use to list descriptions of all recurring transactions you have set up using the "5.1 Recur_Trans" option. You must follow the rules carefully when setting up these recurring transactions for the Alt-L macro to work. Be sure and move to a blank area before executing this macro to avoid having the listing wipe out data. Use 1-2-3's Range Erase command to remove the listing after viewing. Alt-M Returns you to the Modify submenu. Normally used to return to the menus after selecting the "Add/Edit" option from the Modify submenu. Alt-N Returns you to the Recur_Trans submenu. Normally used to return to the menus after selecting the "Add/Edit" option from the Recur_Trans submenu. Alt-Q Returns you to the Query Data submenu. Normally used to return to the menus after selecting the "Freeform" option from the Query Data submenu. Alt-R Use to copy in a set of recurring transactions as entered using the "5.1 Recur_Trans" option. Prompts you for a the range name assigned to the set of transactions, then copies in the named Page 33 range, and converts the dates from @DATE formulas to numbers. This last step prevents the dates from changing when the current month and year are changed. This macro should be executed while in column B if the recurring transactions have been set up exactly as instructed. Alt-S Use as a short cut to the Summarize submenu while in READY mode. Alt-Y Use as a short cut to the "3.1 All" menu option while in READY mode. This macro was included to maximize the "what-if" capabilities of the application. Alt-Z Returns you to the main menu. One of the primary uses of this macro is to return to the main menu after selecting the "7.2 Save" option. Page 34 Appendix B CashFlow Ledger Range Name Listing 1 B296..F298 | DKEY B450 2 B301..F302 | DT F89 ACT C351 | ENTER A446 ADD1ST E182 | ENTERBAL F447 ADDOPT A46 | ERASEHELP E190 AKEY E450 | EXITOPT A27 AMTKEY F450 | FFMHDG A103..F105 BAL F392 | FILEOPT B163 BALANCE F450..F2034 | GRAPHOPT B213 BEGBAL E247 | HELP1 G1..G20 CASHAREA C255..E286 | HELP10 H68..H87 CASHCALC A209 | HELP11 H89..H108 CASHCRIT1 B191..B192 | HELP12 H110..H129 CASHCRIT2 C191..C192 | HELP13 H131..H150 CASHDAYS C255..C285 | HELP14 H152..H171 CASHFLOW A243..F285 | HELP15 H173..H192 CASHFORM1 C201 | HELP2 G68..G87 CASHFORM2 C202 | HELP3 G89..G108 CASHFORMH1 D254 | HELP4 G110..G129 CASHFORMH2 E254 | HELP5 G131..G150 CASHFORMS D254..E254 | HELP6 G152..G171 CASHOPT A207 | HELP7 G173..G192 CASHTABLE C254..E284 | HELP8 G243..G262 CDAYS C204 | HELP9 H1..H20 COUNT F88 | HELPOPT A41 CRIT2 E391..E392 | HMONTH B168 CRITERIA C132..C133 | HOLD B133 D F87 | HOLDMAIN F178..F187 DATA1 B451 | INIT C181 DATECRIT C191..C192 | INP1 E392 DATEINPUT C199 | INP2 E16 DATEM1 B109 | INP3 E16 DATEM2 A72 | INP4 C133 DATEM3 D72 | INPD1 B392 DATEM4 D155 | INPD2 D392 DATER F162 | KEY C340 DAY F85 | LASTOPT A134 DAY2 F86 | LASTOPT2 D134 DB B450..F465 | LASTOPT3 C172 DBLABELS B395..F395 | LOOP B148 DBSUMMARY D188 | LOOP1 C148 DBTITLES A446..A451 | MAIN D7 DCA C107 | MAINOPT A22 DCRIT C132..C133 | MESSAGES F169..F173 DCRIT1 B106..B107 | MHOLD1 D393 DCRIT2 B106..C107 | MHOLD2 C448 DCRIT3 B392..F393 | MHOLD3 D448..D449 DCRIT5 D53..D54 | MHOLD4 D337 DESCKEY D450 | MHOLD5 C449 Page 35 MHOLD6 D290 | SUMFORM1 B79 MHOLD7 D448 | SUMFORM2 B80 MONTH F84 | SUMFORM3 B81 MSSG D448..D449 | SUMFORM4 B78 MSSG1 F169..F170 | SUMHDG D106..D107 MSSG2 F171 | SUMMARY A335..F350 MSSG3 F172 | SUMOPT A57 MSSG4 F173 | SUMTBL C340..C390 MSSG5 F174 | TABLE C339..D350 MSSG8 F175 | TITLES B446..G428 MSSG9 F176 | TKEY C450 NOTICE B5..B18 | UNIQUE C339 OUTPUT C339..B390 | XCOPT B227 OUTPUT2 B395..F445 | XFILE B156 PRINTHELP F190 | XFILENAME B169 PRTOPT B139 | XFILENAME2 B159 QDATE1 E79 | YEAR F83 QDATE2 E81 | \0 C120 QDAY1 C83 | \A C117 QDAY2 C86 | \C C129 QHDG1 A99..F101 | \D C124 QHDG2 A95..F97 | \H C131 QHDG3 A91..F93 | \L F235 QMONTH1 C84 | \M C119 QMONTH2 C87 | \N C116 QYEAR1 C85 | \Q C123 QYEAR2 C88 | \R D235 RECTRANS A288..F334 | \S C125 REVAMT1 F396 | \T F240 REVCOUNT F395..F445 | \X C118 REVDATA B396..F445 | \Y C127 REVIEW A391..F397 | \Z C121 REVOPT A64 | REVPRT E149 | RTCRIT C234..C235 | RTDB A293..A334 | RTLOOP D239 | RTNAME D237 | RTOPT B221 | RTOUTPUT F238 | SDATE1 G52 | SDATE2 G53 | SETOPT A32 | SETUPOPT A32 | SFILENAME E30 | SORTDB B452..F465 | SORTOPT A36 | SUM F111 | SUM2 E111 | SUMALL A59 | SUMBAL F336 | SUMDATA C340..D390 | SUMDATE G51..H54 | SUMDR E339..F342 | SUMFORM D339 | Page 36 Appendix C Summary of Reporting Options There are 8 different reports you can produce using The CashFlow Ledger's menu options. Graphs can also be saved and printed via the Lotus 1-2-3 PrintGraph program. Keep in mind that the only print setting altered by the following menu options is the print Range. If you need to enter special margins or setup strings, you need only do it once, save the modified version of The CashFlow Ledger to a disk file, and they will remain intact for that version. The following summarizes the printing options available in The CashFlow Ledger. 1.5 Print Prints a listing of the entire database exactly as you see it from the Modify submenu. =Current Month=============================================Balance== Apr-85 Transaction Database 125.00 ==================================================================== DATE TRAN # DESCRIPTION ACCOUNT AMOUNT ------------------------------------------------------------------- 01-Apr-85 0 Beginning balance 100 1,500.00 02-Apr-85 500 Rent 630 (500.00) 03-Apr-85 501 Telephone 640 (100.00) 05-Apr-85 504 Insurance 650 (600.00) 05-Apr-85 505 Office supplies 655 (75.00) 05-Apr-85 502 Payroll 625 (900.00) 15-Apr-85 506 Office supplies 655 (125.00) 21-Apr-85 509 Loan pymt - principal 220 (400.00) 21-Apr-85 510 Loan pymt - interest 750 (300.00) 22-Apr-85 2046 Consulting income 430 900.00 22-Apr-85 2045 Computer sale proceeds 420 1,800.00 25-Apr-85 508 Payroll 625 (900.00) 25-Apr-85 507 Office supplies 655 (100.00) 29-Apr-85 511 Magee - CashFlow Ledger 655 (75.00) _____________________________________________________________________ 2.6 Print Prints a listing of the Query section exactly as you see it from the Query submenu. ============================================ACCOUNT =====BALANCE== Transactions and Balance for account number 655 (375.00) ==================================================================== DATE TRAN # DESCRIPTION ACCOUNT AMOUNT 05-Apr-85 505 Office supplies 655 (75.00) 15-Apr-85 506 Office supplies 655 (125.00) Page 37 25-Apr-85 507 Office supplies 655 (100.00) 29-Apr-85 511 Magee - CashFlow Ledger 655 (75.00) _____________________________________________________________________ 3.5.1 Summary Prints a listing of the Summary section exactly as you see it from the Summary submenu. =Current Month=============================================Balance== Apr-85 Summary by Account Number (375.00) ==================================================================== ACCOUNT AMOUNT -------------------------------- 100 1,500.00 220 (400.00) 420 1,800.00 430 900.00 625 (1,800.00) 630 (500.00) 640 (100.00) 650 (600.00) 655 (375.00) 750 (300.00) _____________________________________________________________________ 3.5.2 Account Produces a G/L Listing. To be more specific, for each transaction currently appearing in the Summary section, it prints it's balance, along with all transactions that go to make up that balance. ============================================ACCOUNT =====BALANCE== Transactions and Balance for account number 625 (500.00) ==================================================================== DATE TRAN # DESCRIPTION ACCOUNT AMOUNT 02-Apr-85 500 Rent 630 (500.00) ============================================ACCOUNT =====BALANCE== Transactions and Balance for account numbe 625 (1,800.00) ==================================================================== DATE TRAN # DESCRIPTION ACCOUNT AMOUNT 05-Apr-85 502 Payroll 625 (900.00) 25-Apr-85 508 Payroll 625 (900.00) ============================================ACCOUNT =====BALANCE== Transactions and Balance for account number 655 (375.00) ==================================================================== DATE TRAN # DESCRIPTION ACCOUNT AMOUNT 05-Apr-85 505 Office supplies 655 (75.00) 15-Apr-85 506 Office supplies 655 (125.00) Page 38 25-Apr-85 507 Office supplies 655 (100.00) 29-Apr-85 511 Magee - CashFlow Ledger 655 (75.00) _____________________________________________________________________ 3.5.3 Date_Range Same as 3.5.2 Account except that it only includes transaction falling within a specified time period. 4.2.3 Save Use to save a picture of the last graph viewed on the screen to a disk file for printing via the Lotus 1-2-3 PrintGraph program. 4.5 Print Prints a listing of the Cash Flow Summary section exactly as you see it from the Cash Flow Summary submenu. =Current Month====================================================== Apr-85 Cash Flow Summary ==================================================================== Beginning Balance 0.00 Ending Balance 0.00 Average Balance -83.33 High Balance 1500.00 Low Balance -800.00 Day Cash Balance ----------------------------------------- 1 1500.00 1500.00 2 -500.00 1000.00 3 -100.00 900.00 4 0.00 900.00 5 -1575.00 -675.00 6 0.00 -675.00 7 0.00 -675.00 8 0.00 -675.00 9 0.00 -675.00 10 0.00 -675.00 11 0.00 -675.00 12 0.00 -675.00 13 0.00 -675.00 14 0.00 -675.00 15 -125.00 -800.00 16 0.00 -800.00 17 0.00 -800.00 18 0.00 -800.00 19 0.00 -800.00 20 0.00 -800.00 21 0.00 -800.00 22 2300.00 1500.00 23 0.00 1500.00 24 0.00 1500.00 25 -1300.00 200.00 26 0.00 200.00 Page 39 27 0.00 200.00 28 0.00 200.00 29 -75.00 125.00 30 0.00 125.00 _____________________________________________________________________ 5.1.2 Print Prints a listing of the Recurring Transaction section exactly as you see it from the Recurring Transaction submenu. ==================================================================== 01-Apr-85 Recurring Transactions ==================================================================== DATE TRAN # DESCRIPTION ACCOUNT AMOUNT *------------------------------------------------------------------- * Group # 1 - Transactions occuring every month 02-Apr-85 1 Car Loan - principal pymt 210 (207.80) 03-Apr-85 1 Car Loan - interest pymt 810 (56.79) 03-Apr-85 2 Rent 610 (360.00) * Group #2 - B&O Taxes 06-Apr-85 6 State B&O 624 (1,111.00) 07-Apr-85 7 City B&O 625 (1,111.11) _____________________________________________________________________ 6.7 Print Prints a copy of the on-line help screens. Page 40 Appendix D Building The Account Summary Macro Routines The 123 Ledger is an advanced Lotus 1-2-3 macro application that was recently uploaded to The World of Lotus. This article walks you through the construction of a simplified version of the macro routines used to summarize transactions by account number in The CashFlow Ledger. The techniques it discusses deal with some of the more advanced 1-2-3 features such as @DSUM formulas, macros, and the Data Query, Data Table, and Data Sort commands. The end result is a 1-2-3 application that will post over 1800 transactions to over 100 account numbers, by pressing a single keystroke. And that's on a computer with only 256k of memory. How it all began Last February, the owner of a local CPA firm ask me if I knew a way to use 1-2-3 to post checks. He didn't want anything complicated, just a fast way to enter checks into a database, and an even faster way to summarize the checks by account number. The client he had in mind had several hundred transactions, and a few dozen account numbers. After analyzing the techniques published in various trade journals for doing ledger applications, I concluded that none were appropriate for handling this number of transactions and accounts. A little experimenting uncovered a technique that used a combination of the Data Query Unique, Data Table 1, and Data Sort commands, in conjunction with a single @DSUM formula. Using 1-2-3's macro capabilities, I was able to automate the process so that my client could post or summarize his transactions by pressing a single key. Overview The version of this ledger application that this article will guide you though has three main components, as illustrated in Figure 1. _____________ | | | Macros | |_____________| | | | Summary | |_____________| | | | Database | |_____________| _______________________________ Figure 1 The two macro routines you will build in this article are \A and \S. \A is used to speed up data entry, and the second, \S, summarizes the transactions by account number. Before discussing the macros, we'll need to set up a little database. Page 41 The Database My client didn't want to spend a lot of time entering these transactions. In fact, all he wanted to enter was the date, account number, and amount. Thus the database had only three fields, and looked something like Figure 2. ============================================ Transaction Database ============================================ Date Account# Amount 01-Apr-84 630 (500.00) 03-Apr-84 640 (100.00) 05-Apr-84 625 (900.00) 05-Apr-84 630 (600.00) 05-Apr-84 655 (75.00) 15-Apr-84 660 (125.00) 22-Apr-84 420 1,800.00 22-Apr-84 430 900.00 22-Apr-84 630 (400.00) 25-Apr-84 420 1,100.00 ___________________________________________ Figure 2 You will need to set up this database in order to test the macro routines. For the macro routines to work, you must set up your worksheet exactly as instructed. Begin by setting up the title section as shown in Figure 2. Do this by entering the repeating label, "\=", in cells B79 thru E79, and B81 thru E81. Then enter the label, "Transaction Database", in cell C80. You should adjust the widths of columns A, B, C, and D to 7, 14, 12, and 12 respectively, using the Worksheet Column-Width Set command. Enter the field names, "Date", "Account#", and "Amount", in cells B83, C83, and D83 respectively. To speed up the entering of transactions, set up the following macro. B5: '@date(84,{?}){calc}~ B6: '/rfd~~{right} B7: '{?}{right} B8: '{?}~/rf,2~~ B9: '{down}{left}{left} B10: '/xg\A~ Begin by entering the above labels in cells B5 through B10. Then assign the range name, \A to cell B5 with the following command: /Range Name Create <\A> {Enter} <B5> {Enter} Page 42 To add a transaction, just move the cell pointer to the Date column, hold down the Alt key, and tap the A key. The \A macro works as follows: The macro in cell B5 begins entering an @DATE formula and pauses {?} for the user to type in the month and day (such as 4,1 for the first transaction above), and press ENTER. The macro then completes the @DATE formula, converts it to a number with a {calc}, and enters the number in the cell. Before moving on to the Account# column, the cell is changed to date format by the macro in cell B6. Cell B7 simply waits for the user to press the ENTER key and moves to the right one cell. Cell B8 again waits for the user to press enter, and then formats the cell punctuated(,) with two decimal places. Cell B9 positions the cell pointer for entering the next transaction. And finally, cell B10 loops back up to B5, and the macro is repeated. Control-Break can be used any time to break out of the loop. The Summary Macro (\S) This section shows you how to build a macro that will make the posting of the transactions in the database as easy as Alt-S. The entire macro is listed in Figure 3. ============================================ Summary Macros ============================================ \S /wgrm /xcNAME~ /xcACCT#S~ /xcCALC~ /xcSORT~ /wgra /xq NAME {goto}DB~ /rncDB~{esc}.{end}{right}{end}{down}~ /xr ACCT#S {goto}SUMMARY~ /reSUMDATA~ /dqriDB~cCRIT~oOUTPUT~uq /xr CRIT Account# INPUT Page 43 CALC {goto}TABLE~ /rndTABLE~ /rncTABLE~{left}{end}{down} {right}{end}{up}{right}~ /dtr/dt1TABLE~INPUT~ /xr SORT {down} /dsrd.{right}{end}{down}~pKEY~A~g /xr ____________________________________________ Figure 3 This macro has five main parts; the main macro(\S), and and four sub-macros(NAME, ACCT#S, CALC, AND SORT). CRIT and INPUT are used by the CALC macro to calculate the balances for each account, as will be explained later. We will begin by building the main macro and then go on to build each subroutines it calls. The Main Macro - \S Begin by entering the following labels in the designated cells. B12: '/wgrm B13: '/xcNAME~ B14: '/xcACCT#S~ B15: '/xcCALC~ B16: '/xcSORT~ B17: '/wgra B18: '/xq Assign the range name \S to cell B12 with the following command. /Range Name Create <\S> {Enter} <B12> {Enter} Note that all of these macro routines could have been lumped into a single continuous macro. It is a good programming habit, however, to divide large macros into smaller managable units, and then link them together with subroutine calls. This to make them easier to debug, and much easier to explain. The \S macro works as follows: B12: Simply sets recalculation to manual. This speeds up the execution of the macro subroutines. B13: Calls a macro subroutine that re-assigns a range name to the database to include any new transactions that have been added. B14: Calls a macro subroutine that uses the Data Query Unique command to create a list of all the different account numbers used in the database. This becomes in effect, the new chart of accounts. B15: Calls a subroutine that uses this list of account numbers in conjunction with a single @DSUM formula to do a Data Table 1 analysis. The end result is that each accounts balance appears to the right of Page 44 it's account number. B16: Calls a subroutine that sorts this list of account numbers and balances by account number, in ascending order. B17: Turns automatic recalc back on. B18: Terminates macro execution. This is mainly a documentation step. Now that you've seen an overview of the \S macro, lets begin building the individual components. The NAME Subroutine Begin by entering the following labels in the designated cells. B20: '{goto}DB~ B21: '/rncDB~{end}{right}{end}{down}~ B22: '/xr Next, assign the name, NAME to cell B20 with the following command: /Range Name Create <NAME> {Enter} <B20> {Enter} Before this routine will work, you must assign a range name to the database. You only have to do this once to get the macro working. It does not have to be done every time you summarize. The command to assign this name is: /Range Name Create <DB> {Enter} <B83.D93> {Enter} After the macro in cell B20 moves the cell pointer to the top of the database, B21 then recreates the range name to include all new transaction entries by using the {End} key. Note that if a blank line is left in the database, this routine will not work. The remaining macros deal primarily with the Summary section of this application as shown in Figure 4. ============================================ Summary Area ============================================ Amount Account# ____________________________________________ Figure 4 Before building the remaining macros, you will need to set this section up as follows: Page 45 Enter the repeating label \= in cells B46 thru E46 and cells B48 thru E48. Enter the label "Summary Section" in cell C47. Enter the label "Account#" in cell C51. Enter the label "Amount" in cell D50. This is a good time to save you work to disk if you haven't already done so. The ACCT#S Subroutine This routine uses the Data Query Unique command to create a list of the all the different account numbers used in the database. The neat thing about this routine is that it eliminates the need to maintain a chart of accounts within the application. To build this subroutine, you must enter the labels shown below, set up a criterion range for the Data Query Unique command, and then create a few range names. Begin by entering the following labels in the designated cells. B25: '{goto}SUMMARY~ B26: '/reSUMDATA~ B27: '/dqriDB~cCRIT~oOUTPUT~uq B28: '/xr Assign the name, ACCT#S, to cell B25 with the following command: /Range Name Create <ACCT#S> {Enter} <B25> {Enter} To set up the criterian range, enter the label, "Account#" in cell B30. Then assign the name, "CRIT", to this range with the following command: /Range Name Create <CRIT>{Enter}<B30.B31>{Enter} This criteria range will also be used by the Data Table analysis routine that follows. This is why the Account# field was used. You will also need to create the following range names for this macro to work (Note that the principal of always using range names in macros, rather than specific cell references, is strictly adhered to in this application. It is absolutly essential that you master the named range concept if you want to become proficient with macros.) SUMMARY A46 OUTPUT C51..C78 SUMDATA C52..D78 (Note: A listing of all named ranges is included at the end of this article.) The ACCT#S routine works as follows: B25: Moves the cell pointer to the top of the Summary Section. Page 46 B26: Erases all data from the Summary Section to make room for the new list of account numbers and balances. B27: Begins by clearing the Data Query settings (/dqr), and then sets up the input, criteria, and output ranges for the Data Query Unique. DB is the name assigned to the transaction database by the NAME subroutine. CRIT is the name assigned to the criterion range (B30..B31). Cell B31 was left blank so no transactions will be screened out. And OUTPUT is the name assigned to area where we want the list of account numbers to appear (C51..C78). Now we need a way to calculate the balance for each account number in this list. That is where CALC comes in. The CALC Subroutine This routine is the heart of the entire application. It is centered around a single @DSUM formula. The steps you will go through to set up this routine are as follows: 1) Enter @DSUM formula 2) Set up Data Table macro; and 3) Create range names used by the macro The @DSUM formula you need to enter in cell D51 is as follows: @DSUM(DB,2,CRIT) This just tells 1-2-3 to add up the numbers in the amount column for all database entries that meet the criteria. You may recall, that in the previous section, we assigned the name CRIT to the range listed below: B30: 'Account# B31: With cell B31 blank, the above @DSUM formula will give us the total amount for all transactions in the database. By plugging the different account numbers into cell B31, this @DSUM formula will tell us the totals for each account. Lotus 1-2-3 just happens to have a command that will take a list of entries, plug them one at a time into a designated cell, recalculate a formula that is dependant upon that designated cell, and place the result of the calculation just to the right of the entries in the list. In case you haven't already guessed, I'm referring to the Data Table 1 command. To set up the macro routine, enter the following labels in the designated cells: B34: '{goto}TABLE~ B35: '/rndTABLE~ B36: '/rncTABLE~{left}{end}{down} B37: '{right}{end}{up}{right}~ B38: '/dtr/dt1TABLE~INPUT~ B39: '/xr Assign the name, CALC to cell B34 with the following command: Page 47 /Range Name Create <CALC> {Enter} <B34> {Enter} The remaining two range names are necessary to do a data table analysis from within the macro. The first name is TABLE, and it should be assigned to the range C51..D58. This is the answer the macro will give to the first question asked by 1-2-3's Data Table 1 command (Enter Table Range:) The final name is INPUT, and it must be assigned to the cell in the criteria range (CRIT) B31. This is the location where 1-2-3's Data Table command will copy each account number in the list. When executed, the CALC macro subroutine operates as follows: B34: Moves the cell pointer to the top of the Data Table range. B35: Deletes the range name TABLE. B36 & B37: Recreates the name TABLE to include the entire list of account numbers created by the latest Data Query Unique command, as issued in ACCT#S. The direction keys find the bottom of the list by going around it, and coming up from the bottom. This technique was used because I've found that users occassionally omit the account number from entries. This creates a blank space in the list of account numbers. B38: Clears the Data Table settings (/dtr) to speed up processing, and then executes the Data Table 1 command that computes the totals for each account number as follows: The Data Table 1 command takes each account number in the first column of the table range(TABLE) and plugs it into the cell INPUT. The @DSUM formula at the top of the TABLE range calculates the total amounts for all transactions that match the account number currently in the cell we called INPUT. As each account number in TABLE is plugged into INPUT, the @DSUM formula is recalculated, and the result is placed in the column to the right of the account numbers in the range TABLE. I'll understand if that doesn't sink in the first time you read it. If you have followed along step by step, however, you will have the opportunity to see this macro in action, and this should help shed some light on what's happening. Once we have the totals for all account numbers, the final step is to sort the list by account number. This is the job of the SORT routine. You might want to save your work to disk again if you haven't done so recently. The SORT Routine You are coming down the home stretch now. Begin building the SORT routine by entering the following labels: B41: {down} B42: /dsrd.{right}{end}{down}~pKEY~A~g B43: /xr Assign the name SORT to cell B41 with the following command: Page 48 /Range Name Create <SORT> {Enter} <B41> {Enter} The only thing left to complete this routine is to assign the name KEY to some cell in column C. Do this with the following command: /Range Name Create <KEY> {Enter} <C55> {Enter} When executed, the SORT macro routine does the following: B41: Moves the cell pointer to the top of the range to be sorted. B42: Begins by clearing the Data Sort settings (/dsr). Then it defines the Data-Range by anchoring the cell pointer at the current cell, moving right one cell, and then doing an {End} {Down} to reach the bottom of the list. The Primary key is specified using the named range, KEY, set up earlier, and finally, the Sort is executed. Testing The Macro If you've set up the database, the summary section, and built all the macros, you are ready to test the application. Before doing this, you might want to doublecheck you range names. Figure 5 contains a table that will serve as a handy reference for this purpose. ============================================= The 123 Ledger Range Names ============================================= ACCT#S B25 CALC B34 CRIT B30..B31 DB B83..D93 INPUT B31 KEY C51 NAME B20 OUTPUT C51..C78 SORT B41 SUMDATA C52..D78 SUMMARY A46 TABLE C51..D58 \A B5 \S B12 _____________________________________________ Figure 5 If they all match up, give the macro a try by pressing Alt-S. The resulting Summary section should correspond to Figure 6. If not, make sure that the field name (Account#) in the criterion range, is entered exactly as it is in both the database, and the summary section. ============================================= Summary Section ============================================= Page 49 Amount Account# 1100 420 2900 430 900 625 -900 630 -1500 640 -100 655 -75 660 -125 _____________________________________________ Figure 6 Don't forget about 1-2-3's STEP mode when trying to get these macros to work. To use it, hold down the Alt key and tap the F1 key before executing the Alt-S macro. You will see the STEP indicator come on at the bottom of your screen. Now press Alt-S. Nothing happens. Begin pressing the space bar. Note that each time you do, 1-2-3 processes one more keystroke in the Alt-S macro. See your manual for more details if necessary. Page 50 Appendix E Building The Cash Flow Summary Routines This article shows you how to build the formulas and macros that form the heart of the cash flow summary routines in The CashFlow Ledger. Wouldn't it be nice if the same data entered into a Cash Management system could be used as input to a General Ledger system. This would allow you to enter the transactions you expect to occur in the cash management system to produce fairly accurate short term cash flow projections. Then as time passes and the transactions actually occur, they could be transferred to the G/L system, thus eliminating the need to enter them twice. The CashFlow Ledger uses Lotus 1-2-3's date arithmatic features to take a database of proposed transactions and produce a cash flow forecast. Both future and historical transactions are maintained in the same database so there is no need to "transfer" them. The two types of transactions are distinguished by the transaction date and number. The simplified version of The CashFlow Ledger this article steps you through draws upon a broad range of 1-2-3's capabilities including: Spreadsheet, Database, Graphics, Macros, and Date-Arithmatic. As with The 123 Ledger, The CashFlow Ledger uses a combination of @DSUM formulas and the Data Table 1 command to do it's summarization. This article should probably be avoided by beginning 1-2-3 users who are not willing to spend a few hours experimenting. Page 51 Overview The application you are going to build has the following layout: _____________ | | | Macros & | | Variables | |_____________| | | | Cash Flow | | Summary | |_____________| | | | Transaction | | Database | |_____________| _______________________________ Figure A You will begin by setting up a sample database of future cash transactions, with the help of a data entry macro. You will then go through the steps necessary to produce a cash flow projection from this database including a couple graphs. After you have done these things once manually, you will be shown how to automate the process with macros, and thus maximize the what-if graphics capabilities of the application. Make sure you are starting with a blank worksheet by entering the /Worksheet Erase Yes command. Adjust the widths of the columns using the /Worksheet Column-Width Set command, as follows: Column Width ------ ----- A 5 B 11 C 9 D 25 E 11 F 11 The Database Page 52 Assume you are in a position where you need to do a daily cash flow projection for the month of June. Figure B contains a listing of the cash related transactions you expect to take to place during this month. The ****'s in the Transact# column will be replaced by the actual transaction numbers as they actually occur. ================================================================= Transaction Database ================================================================= Date Transact# Description Account# Amount 01-Jun-85 **** Supplies 630 (500.00) 03-Jun-85 **** Phone 640 (100.00) 05-Jun-85 **** Wages 625 (600.00) 05-Jun-85 **** Supplies 630 (50.00) 05-Jun-85 **** Interest 655 (75.00) 15-Jun-85 **** Sales 420 1,700.00 22-Jun-85 **** Misc. 660 (125.00) 22-Jun-85 **** Sales 420 1,800.00 22-Jun-85 **** Utilities 430 (150.00) 25-Jun-85 **** Rent 630 (400.00) 30-Jun-85 **** Sales 420 1,100.00 _______________________________________________________________ Figure B - Transaction Database. You can begin setting up this database by entering the repeating label, \= in cells A78 through F78, and A80 through F80. Then enter the label 'Transaction Database in cell D79, and the field names in the following cells as indicated: B82: ^Date C82: 'Transact# D82: ^Description E82: "Account# F82: "Amount The following macro can be used to help speed up transaction entry. The first step to setting it up is to enter the following labels in the cells indicated: A3: '\A B3: '@DATE(85,6,{?}){calc}~ B4: '/rfd~~{right} B5: '{?}{right} B6: '{?}{right} B7: '{?}{right} B8: '{?}~/rf,2~~ B9: '{down}{left}{left}{left}{left} B10: '/xg\A~ The next step is to assign the range name \A to cell B3 with the Page 53 command: /Range Name Create <\A> {Enter} <B3> {Enter} You can now to begin entering transactions by moving the cell pointer to B83, holding down the Alt key, and tapping the A key. To enter the date, all you have to do is type in the day number and press enter. The macro in cell B3 enters the rest of the @DATE formula necessary to calculate the 1-2-3 date, and converts the formula to a number by pressing the CALC key. The macro in cell B4 formats the cell to display the number as a date and then moves you on to the Transact# column. The macros in cells B5, B6, and B7 simply pause until you press ENTER, and then move right one cell. For purposes of this article, the Transact# and Description information need not be entered. They were included only for illustration purposes. You can simply press the ENTER key to skip over these items while entering the transactions if you like. B8 pauses for input, enters the number in the cell, and formats it (,) with 2 decimal places. B9 positions the cursor for entering the next transaction and B10 causes macro control to loop back up to cell B3. You can break out of this macro at any time by holding down the "Ctrl" key and tapping the "Break" key. After entering the transactions, assign this database the named range DB with the following command: /Range Name Create <DB> {Enter} <B82..F93> {Enter} The Cash Flow Projection You are now ready to set up and enter the Data Table 1 command that will tell us the daily cash flow and daily cash balances resulting from these transactions. The first thing you need to do is set up the Cash Flow Projection area as shown in Figure C. =============================================================== Cash Flow Projection =============================================================== Day CashFlow Balance 1 2 3 4 5 6 7 8 9 10 11 Page 54 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 _____________________________________________________________ Figure C - Cash Flow Projection Workarea. Begin setting up this section by entering the repeating label, \= in cells A37 through F37, and A39 through F39. Then enter the following labels in the cells indicated: D38: 'Cash Flow Projection C41: "Day D41: "CashFlow E41: "Balance To list the days of the month in column C, enter the following command: /Data Fill <C43..C72> {Enter} <1> {Enter} {Enter} {Enter} Before entering the @DSUM formulas at the top of this table, you will need to set up some formulas and criteria. Begin this step by entering the following labels in the cells indicated: C27: 'BegDate C28: 'EndDate C29: 'Year C30: 'Month C31: 'Day C32: 'BegBal These labels can then be used to assign range names to the adjacent cells with the following command: /Range Name Labels Right <C27..C32> {Enter} BegDate will be used to calculate the 1-2-3 numeric date for the first day of the month being forecast. EndDate will be used to calculate the 1-2-3 numeric date for various days throughout the month. For purposes of this example, enter the following values for Year, Month, Day, and BegBal in the cells indicated below: Page 55 D29: 85 D30: 6 D31: 1 D32: 1000 The formula you need to enter in cell D27 to calculate the BegDate is: @DATE(YEAR,MONTH,1) The number that appears (31199) when you enter this formula is the number of days from January 1, 1900 to June 1, 1985. The formula you need to enter in cell D28 for the EndDate is: @DATE(YEAR,MONTH,DAY) You may find it helpful to format these two cells with the following command: /Range Format Date 1 <D27..D28> {Enter} If entered correctly they both should read: 01-Jun-85 The CashFlow @DSUM Formula What we need is a way to calculate the total of all transactions for a specific day of the month. One way to do this is by setting up an @DSUM formula that tests each transaction date against some "other" date. In our case, this "other" date will be EndDate, which is calculated by combining YEAR, MONTH, and DAY. First we need to set up a criterian section by entering the label ^Date in cell F27. Assign this cell and the one below it the range name CRIT1 with the following command: /Range Name Create <CRIT1> {Enter} <F27..F28> {Enter} Now move down to cell D42 and enter the following formula: @DSUM(DB,4,CRIT1) Remember that DB was the name we assigned to the database. The 4 in this formula is referred to as the "Column Offset". It tells 1-2-3 to add up the Amount column in the database. This often confuses people since Amount is the 5th column in the database. Just remember that when you are calculating a column offset, you start counting with 0 rather than 1. If you entered the formula correctly, it should show a value of 2600. This is the total of all transactions in the database. Now we need to enter a formula in the criteria cell to "filter" out all transactions except those for a specific day. This can be done by entering the following formula in cell F28: +B83=$ENDDATE Remember that one of the rules to properly setting up criterian formulas is that cell references to fields in the database must refer to the first record. The "+B83" refers to the Date of the first record in the Page 56 database. The $ must be placed in front of ENDDATE to make the reference to that cell absolute. This is necessary for the Data Table 1 command to function properly. As soon as you enter this formula, the @DSUM formula should have recalculated to -500. Because ENDDATE is currently June 1, the @DSUM formula is indicating the total of all transactions occuring on that date. If you enter the number 5 in cell D31, ENDDATE will change to 05-Jun-85, and the @DSUM formula will tell you the total of all transactions for that date. If the formula is not working correctly, make sure that you typed in the field name Date exactly as you typed it in the database. To be sure of this, I normally use the Copy command to enter the criteria field names. Once it is working correctly, you may want to experiment with it a bit. However, I recomend you first save your work to disk. Each time you place a new day number in cell D31, the @DSUM formula tells you the total of the transactions for that day. In a few moments, you will be entering a Data Table 1 command that takes the day numbers 1 - 30 listed in column C, plugs them one by one into cell D31, recalculates the @DSUM formula, and places the result in column D. Before issuing this command, you need to set up the @DSUM formula to calculate the cash balance for each day as well. I don't think many people realize that more than one formula can be used in the same Data Table 1 command. The Daily Balance @DSUM Formula Before entering the formula, we will need to set up the criterian section. Begin this by entering the label ^Date in cell F30. Assign the name CRIT2 to the range F30..F31 with the following command: /Range Name Create <CRIT2> {Enter} <F30..F31> {Enter} Now enter the following formula in cell E42: @DSUM(DB,4,CRIT2)+$BEGBAL This formula tells 1-2-3 to add up the Amounts of all transactions meeting the criterian at CRIT2, and add this total to the amount in the cell called BEGBAL. Again, the reference to BEGBAL must be made absolute with the $, in order for the Data Table command to work correctly. If entered correctly, the current value of this formula should be 3600 (2600+1000). To compute the cash balance for a specific day, we will need to add up all transactions from the first of the month (BEGDATE) to a specific day of the month (ENDDATE), and then add this total to the beginning balance (BEGBAL). This can be accomplished by entering the following formula in cell F31: +B83>=$BEGDATE#AND#B83<=$ENDDATE Again, the dollar signs are required for the Data Table 1 command to work correctly when used in conjunction with an @DSUM formula. Enter the number 1 in cell D31, and the current value of the "Balance" @DSUM formula should switch to 500. In other words, the cash balance at Page 57 the end of the first day of June is expected to be 500. To see what the cash balance is expected to be at the end of the 5th day, simply enter a 5 in cell D31. You may want to experiment with this one a bit also. When the Data Table command is issued, it will take the list of day numbers in column C, and plug them one at a time into cell D31. Each time the "Balance" @DSUM formula will be recalculated and the values placed in column E. Entering the Data Table 1 Command I recomend that you save you work to disk one last time before entering the following command: /Data Table 1 <C42..E72> {Enter} <D31> {Enter} In a moment or two, you should see the table filled in with June's projected daily cash flow and daily balance data as shown in Figure D. ============================================================= Cash Flow Projection ============================================================= Day CashFlow Balance -725 -325 1 -500 500 2 0 500 3 -100 400 4 0 400 5 -725 -325 6 0 -325 7 0 -325 8 0 -325 9 0 -325 10 0 -325 11 0 -325 12 0 -325 13 0 -325 14 0 -325 15 1700 1375 16 0 1375 17 0 1375 18 0 1375 19 0 1375 20 0 1375 21 0 1375 22 1525 2900 23 0 2900 24 0 2900 25 -400 2500 26 0 2500 27 0 2500 28 0 2500 29 0 2500 30 1100 3600 _____________________________________________________________ Page 58 Figure D - June '85 Cash Flow Projection. Graphing the Data The format of this data lends itself nicely to graphic analysis. The line graph of the daily cash flow shown in Figure E can be produced by entering the following commands: /Graph X <C43..C72> {Enter} A <D43..D72> {Enter} Options Format Graph Lines Quit Titles First <Daily Cash Flow> {Enter} Scale Skip <3> {Enter} Quit View Quit The Options Scale Skip command was used to tell 1-2-3 to display every third item in the X range. Another useful graph that can be produced from the above Cash Flow Projection is a line graph of the daily balance. Before defining this graph however, you should enter the following commands to save the above graph specifications for future use: /Graph Name Create <CASHFLOW> {Enter} Quit This command adds all of the above graph specifications into a "catelog". This catelog allows us to create new graphs and still be able to recall the CASHFLOW graph specifications with the Graph Name Use command. The following commands can now be entered to produce the Daily Balance graph shown in Figure F: /Graph A <E43..E72> {Enter} Options Titles First <Daily Balance> {Enter} Quit View Quit You should also add these graph specifications into the graph "catelog" referred to above under the name BALANCE with the following command: /Graph Name Create <BALANCE> {Enter} Quit Automating all this with Macros Page 59 The "what-if" graphics capabilities of this application can be greatly increased by reducing the process of updating the Cash Flow Projection to a single keystroke. With such a macro, you could make slight changes to the transaction database and see the resulting effect on cash flow by pressing just one key. Before setting up this macro, there are two more range names you will need to define. Assign the name CASHFLOW to the top of the Cash Flow Projection area with the command: /Range Name Create <CASHFLOW> {Enter} <A37> {Enter} Next you will need to assign the name DATETABLE to the area used by the Data Table 1 command. This can be done by entering the following command: /Range Name Create <DATETABLE> {Enter} <C42..E72> {Enter} You are now ready to begin setting up the macro by entering the following labels in the cells indicated: A14: '\G B14: '{goto}DB~ B15: '/rncDB~{esc}.{end}{right}{end}{down}~ B16: '{goto}CASHFLOW~ B17: '/dtr/dt1DATETABLE~DAY~ B18: '/gnuBALANCE~q Assign cell B14 the name \G with the following command: /Range Name Create <\G> {Enter} <B14> {Enter} When executed, this macro moves the cell pointer to the top of the database. Then the macro in cell B15 recreates the named range DB to include any new transactions that have been entered since the last time the macro was executed. Note that the use of the {end} key in this macro makes it necessary that you do not skip any rows while entering transactions in the database. After "renaming" the database, the macro in cell B16 moves the cell pointer to the top of the Cash Flow Projection area. The macro in cell B17 begins by resetting all Data Table specifications. This is done simply to speed up macro execution a bit. The macro then goes on to issue the Data Table 1 command that will compute the daily cash flow and balance amounts. Finally, the macro in B18 issues the Graph Name Use command to call up the Daily Balance graph specifications defined earlier. Note that none of these macros contain any specific cell references such as "A38". In all cases, range names were assigned to the cells and then used in the macros. If you don't already do this, I would like to emphasize again, that the benefits realized from sticking to this habit religiously have been substantial. Before changing the transaction database, you may want to save your work to disk, and then give the macro a try by pressing Alt-G. If the macros are working correctly the Daily Balance Graph should be displayed Page 60 exactly as it was the first time. If not, you should verify that the range names are defined as indicated in Figure G. ================================================== The Cash Flow Ledger Range Names ================================================== BEGBAL D32 BEGDATE D27 CASHFLOW A37 CRIT1 F27..F28 CRIT2 F30..F31 DATETABLE C42..E72 DAY D31 DB B82..F93 ENDDATE D28 MONTH D30 YEAR D29 \A B3 \G B14 __________________________________________________ Figure G - CashFlow Ledger Range Names. Let's see if we can't do something about that negative cash flow during the early part of the month. Move to cell B85 and enter the following command to insert a row: /Worksheet Insert Row {Enter} Press Alt-A and enter the following transaction: Date Transact# Description Account# Amount 04-Jun-85 **** NMI Sales 420 400.00 The NMI stands for: Necessity is the Mother of Invention. So much for my sense of humor. After entering the transaction, you can stop macro execution by holding down the "Ctrl" key and tapping the "Break" key. Now press Alt-G to view the new projection. The daily balance should no longer go below zero. Conclusion When I began snow skiing, the first thing I learned was how to fall. Once I overcame the fear of wiping out, it became easier to push myself and improve my skills. When I began learning 1-2-3, one of the first things I learned was how to make it beep at me. Soon thereafter I learned what it took to wipe things out to the point where I couldn't retrieve them. Once I had these things down, it was much easier to experiment with new ways of doing things. By removing the barriers to experimentation, I was able to develop applications such as this one through what I call, "What-if" learning. Page 61 Appendix F Trouble Shooting In some cases, you will enter commands while within the "Add/Edit" option that screw up the key range name, "DB". This is the "Achilles heel" of The CashFlow Ledger. If, when you select the "Quit" option from the Modify submenu, you get an error message indicating the range name "DB" does not exist or is invalid, you will need to do the following: - Press the "Esc" key to clear the error. - Enter the command: /Range Name Create <DB> <ENTER> When it asks for a range, use the direction keys to highlight the entire transaction database, including the field names (Date, Tran#, Descr, etc.). The reason this is necessary is because when you select this "Quit" option, the macros go through the following steps: - Clear titles and move cell pointer to upper left corner of the named range, "DB". This should place the cell pointer in cell containing the field name, "Date". - Deletes the range name, "DB". - Re-creates the range name, "DB", and assigns it a range encompassing the entire transaction database. If you get the message, "Too many records for output" You will need to expand either the Query or Summary sections using the 5.6 eXpand/Contract options located in the "Setup" submenu. Press Esc and then Alt-Z to return to main menu. If "Balance" in upper right corner of the transaction database screen displays an "ERR", use the "5.1 Formfix" option to correct. If you have entered a large number of transactions and recalculation is slowing down considerable, consider issuing the 1-2-3 command /Worksheet Global Recalculation Manual to turn off autorecalc. This command must be entered while in READY mode (as indicated in the upper right corner of your screen.) Page 62